How to copy data from another workbook excel

Question

I already have a macro that creates sheets and some other stuff  After a sheet has been created do I want to call another macro that copies data from a second excel  its open  to first and active excel file   First I want to copy to headers  but I cant get that to work - keep getting errors   Sub CopyData sheetName as String    Dim File as String  SheetData as String    File    my file xls    SheetData    name of sheet where data is       Copy headers to sheetName in main file   Workbooks File  Worksheets SheetData  Range  A1   Select    fails here  Method Select for class Range failed   Workbooks File  Worksheets SheetData  Range Selection  Selection End xlToRight   Select   Workbooks File  Worksheets SheetData  Selection Copy ActiveWorkbook Sheets sheetName  Cells 1  1  End Sub   What is wrong    I really want to avoid having to make  my file xls  active   Edit  I had to give it up and copy the SheetData to target file as a new sheet  before it could work  Find and select multiple rows

User · Answer

Would you be happy to make  my file xls  active if it didn t affect the screen  Turning off screen updating is the way to achieve this  it also has performance improvements  significant if you are doing looping while switching around worksheets   workbooks    The command to do this is       Application ScreenUpdating   False   Don t forget to turn it back to True when your macros is finished

User · Answer

Best practice is to open the source file  with a false visible status if you don t want to be bother  read your data and then we close it   A working and clean code is avalaible on the link below    http   vba-useful blogspot fr 2013 12 how-do-i-retrieve-data-from-another html

User · Answer

Would you be happy to make  my file xls  active if it didn t affect the screen  Turning off screen updating is the way to achieve this  it also has performance improvements  significant if you are doing looping while switching around worksheets   workbooks    The command to do this is       Application ScreenUpdating   False   Don t forget to turn it back to True when your macros is finished

User · Answer

Best practice is to open the source file  with a false visible status if you don t want to be bother  read your data and then we close it   A working and clean code is avalaible on the link below    http   vba-useful blogspot fr 2013 12 how-do-i-retrieve-data-from-another html

User · Answer

I don t think you need to select anything at all  I opened two blank workbooks Book1 and Book2  put the value  A  in Range  A1   of Sheet1 in Book2  and submitted the following code in the immediate window -   Workbooks 2  Worksheets 1  Range  A1   Copy Workbooks 1  Worksheets 1  Range  A1    The Range  A1   in Sheet1 of Book1 now contains  A    Also  given the fact that in your code you are trying to copy from the ActiveWorkbook to  myfile xls   the order seems to be reversed as the Copy method should be applied to a range in the ActiveWorkbook  and the destination  argument to the Copy function  should be the appropriate range in  myfile xls

User · Answer

I was in need of copying the data from one workbook to another using VBA  The requirement was as mentioned below 1  On pressing an Active X button open the dialogue to select the file from which the data needs to be copied  2  On clicking OK the value should get copied from a cell   range to currently working workbook   I did not want to use the open function because it opens the workbook which will be annoying  Below is the code that I wrote in the VBA  Any improvement or new alternative is welcome   Code  Here I am copying the A1 C4 content from a workbook to the A1 C4 of current workbook      Private Sub CommandButton1 Click           Dim BackUp As String         Dim cellCollection As New Collection         Dim strSourceSheetName As String         Dim strDestinationSheetName As String         strSourceSheetName    Sheet1   Mention the Source Sheet Name of Source Workbook         strDestinationSheetName    Sheet2   Mention the Destination Sheet Name of Destination Workbook           Set cellCollection   GetCellsFromRange  A1 C4    Mention the Range you want to copy data from Source Workbook          With Application FileDialog msoFileDialogOpen               AllowMultiSelect   False              Show               Filters Add  Macro Enabled Xl      xlsm    1              For intWorkBookCount   1 To  SelectedItems Count                 Dim strWorkBookName As String                 strWorkBookName    SelectedItems intWorkBookCount                  For cellCount   1 To cellCollection Count                     On Error GoTo ErrorHandler                     BackUp   Sheets strDestinationSheetName  Range cellCollection Item cellCount                       Sheets strDestinationSheetName  Range cellCollection Item cellCount     GetData strWorkBookName  strSourceSheetName  cellCollection Item cellCount                       Dim strTempValue As String                     strTempValue   Sheets strDestinationSheetName  Range cellCollection Item cellCount   Value                     If  strTempValue    0   Then                         strTempValue   BackUp                     End If Sheets strDestinationSheetName  Range cellCollection Item cellCount     strTempValue  ErrorHandler                      If  Err Number  lt  gt  0  Then                             Sheets strDestinationSheetName  Range cellCollection Item cellCount     BackUp                         Exit For                     End If                 Next cellCount             Next intWorkBookCount         End With      End Sub      Function GetCellsFromRange RangeInScope As String  As Collection         Dim startCell As String         Dim endCell As String         Dim intStartColumn As Integer         Dim intEndColumn As Integer         Dim intStartRow As Integer         Dim intEndRow As Integer         Dim coll As New Collection          startCell   Left RangeInScope  InStr RangeInScope       - 1          endCell   Right RangeInScope  Len RangeInScope  - InStr RangeInScope                intStartColumn   Range startCell  Column         intEndColumn   Range endCell  Column         intStartRow   Range startCell  Row         intEndRow   Range endCell  Row          For lngColumnCount   intStartColumn To intEndColumn             For lngRowCount   intStartRow To intEndRow                 coll Add  Cells lngRowCount  lngColumnCount  Address RowAbsolute  False  ColumnAbsolute  False               Next lngRowCount         Next lngColumnCount          Set GetCellsFromRange   coll     End Function      Function GetData FileFullPath As String  SheetName As String  CellInScope As String  As String         Dim Path As String         Dim FileName As String         Dim strFinalValue As String         Dim doesSheetExist As Boolean          Path   FileFullPath         Path   StrReverse Path          FileName   StrReverse Left Path  InStr Path       - 1           Path   StrReverse Right Path  Len Path  - InStr Path         1            strFinalValue         amp  Path  amp       amp  FileName  amp       amp  SheetName  amp        amp  CellInScope         GetData   strFinalValue     End Function

User · Answer

I don t think you need to select anything at all  I opened two blank workbooks Book1 and Book2  put the value  A  in Range  A1   of Sheet1 in Book2  and submitted the following code in the immediate window -   Workbooks 2  Worksheets 1  Range  A1   Copy Workbooks 1  Worksheets 1  Range  A1    The Range  A1   in Sheet1 of Book1 now contains  A    Also  given the fact that in your code you are trying to copy from the ActiveWorkbook to  myfile xls   the order seems to be reversed as the Copy method should be applied to a range in the ActiveWorkbook  and the destination  argument to the Copy function  should be the appropriate range in  myfile xls

User · Answer

Would you be happy to make  my file xls  active if it didn t affect the screen  Turning off screen updating is the way to achieve this  it also has performance improvements  significant if you are doing looping while switching around worksheets   workbooks    The command to do this is       Application ScreenUpdating   False   Don t forget to turn it back to True when your macros is finished

User · Answer

I don t think you need to select anything at all  I opened two blank workbooks Book1 and Book2  put the value  A  in Range  A1   of Sheet1 in Book2  and submitted the following code in the immediate window -   Workbooks 2  Worksheets 1  Range  A1   Copy Workbooks 1  Worksheets 1  Range  A1    The Range  A1   in Sheet1 of Book1 now contains  A    Also  given the fact that in your code you are trying to copy from the ActiveWorkbook to  myfile xls   the order seems to be reversed as the Copy method should be applied to a range in the ActiveWorkbook  and the destination  argument to the Copy function  should be the appropriate range in  myfile xls

User · Answer

I was in need of copying the data from one workbook to another using VBA  The requirement was as mentioned below 1  On pressing an Active X button open the dialogue to select the file from which the data needs to be copied  2  On clicking OK the value should get copied from a cell   range to currently working workbook   I did not want to use the open function because it opens the workbook which will be annoying  Below is the code that I wrote in the VBA  Any improvement or new alternative is welcome   Code  Here I am copying the A1 C4 content from a workbook to the A1 C4 of current workbook      Private Sub CommandButton1 Click           Dim BackUp As String         Dim cellCollection As New Collection         Dim strSourceSheetName As String         Dim strDestinationSheetName As String         strSourceSheetName    Sheet1   Mention the Source Sheet Name of Source Workbook         strDestinationSheetName    Sheet2   Mention the Destination Sheet Name of Destination Workbook           Set cellCollection   GetCellsFromRange  A1 C4    Mention the Range you want to copy data from Source Workbook          With Application FileDialog msoFileDialogOpen               AllowMultiSelect   False              Show               Filters Add  Macro Enabled Xl      xlsm    1              For intWorkBookCount   1 To  SelectedItems Count                 Dim strWorkBookName As String                 strWorkBookName    SelectedItems intWorkBookCount                  For cellCount   1 To cellCollection Count                     On Error GoTo ErrorHandler                     BackUp   Sheets strDestinationSheetName  Range cellCollection Item cellCount                       Sheets strDestinationSheetName  Range cellCollection Item cellCount     GetData strWorkBookName  strSourceSheetName  cellCollection Item cellCount                       Dim strTempValue As String                     strTempValue   Sheets strDestinationSheetName  Range cellCollection Item cellCount   Value                     If  strTempValue    0   Then                         strTempValue   BackUp                     End If Sheets strDestinationSheetName  Range cellCollection Item cellCount     strTempValue  ErrorHandler                      If  Err Number  lt  gt  0  Then                             Sheets strDestinationSheetName  Range cellCollection Item cellCount     BackUp                         Exit For                     End If                 Next cellCount             Next intWorkBookCount         End With      End Sub      Function GetCellsFromRange RangeInScope As String  As Collection         Dim startCell As String         Dim endCell As String         Dim intStartColumn As Integer         Dim intEndColumn As Integer         Dim intStartRow As Integer         Dim intEndRow As Integer         Dim coll As New Collection          startCell   Left RangeInScope  InStr RangeInScope       - 1          endCell   Right RangeInScope  Len RangeInScope  - InStr RangeInScope                intStartColumn   Range startCell  Column         intEndColumn   Range endCell  Column         intStartRow   Range startCell  Row         intEndRow   Range endCell  Row          For lngColumnCount   intStartColumn To intEndColumn             For lngRowCount   intStartRow To intEndRow                 coll Add  Cells lngRowCount  lngColumnCount  Address RowAbsolute  False  ColumnAbsolute  False               Next lngRowCount         Next lngColumnCount          Set GetCellsFromRange   coll     End Function      Function GetData FileFullPath As String  SheetName As String  CellInScope As String  As String         Dim Path As String         Dim FileName As String         Dim strFinalValue As String         Dim doesSheetExist As Boolean          Path   FileFullPath         Path   StrReverse Path          FileName   StrReverse Left Path  InStr Path       - 1           Path   StrReverse Right Path  Len Path  - InStr Path         1            strFinalValue         amp  Path  amp       amp  FileName  amp       amp  SheetName  amp        amp  CellInScope         GetData   strFinalValue     End Function

User · Answer

Two years later  Found this on Google  so for anyone else     As has been mentioned above  you don t need to select anything  These three lines   Workbooks File  Worksheets SheetData  Range  A1   Select Workbooks File  Worksheets SheetData  Range Selection  Selection End xlToRight   Select Workbooks File  Worksheets SheetData  Selection Copy ActiveWorkbook Sheets sheetName  Cells 1  1    Can be replaced with  Workbooks File  Worksheets SheetData  Range Workbooks File  Worksheets SheetData     Range  A1    Workbooks File  Worksheets SheetData  Range  A1   End xlToRight   Copy   Destination  ActiveWorkbook Sheets sheetName  Cells 1  1   This should get around the select error

User · Answer

Two years later  Found this on Google  so for anyone else     As has been mentioned above  you don t need to select anything  These three lines   Workbooks File  Worksheets SheetData  Range  A1   Select Workbooks File  Worksheets SheetData  Range Selection  Selection End xlToRight   Select Workbooks File  Worksheets SheetData  Selection Copy ActiveWorkbook Sheets sheetName  Cells 1  1    Can be replaced with  Workbooks File  Worksheets SheetData  Range Workbooks File  Worksheets SheetData     Range  A1    Workbooks File  Worksheets SheetData  Range  A1   End xlToRight   Copy   Destination  ActiveWorkbook Sheets sheetName  Cells 1  1   This should get around the select error

User · Answer

I don t think you need to select anything at all  I opened two blank workbooks Book1 and Book2  put the value  A  in Range  A1   of Sheet1 in Book2  and submitted the following code in the immediate window -   Workbooks 2  Worksheets 1  Range  A1   Copy Workbooks 1  Worksheets 1  Range  A1    The Range  A1   in Sheet1 of Book1 now contains  A    Also  given the fact that in your code you are trying to copy from the ActiveWorkbook to  myfile xls   the order seems to be reversed as the Copy method should be applied to a range in the ActiveWorkbook  and the destination  argument to the Copy function  should be the appropriate range in  myfile xls

User · Answer

Would you be happy to make  my file xls  active if it didn t affect the screen  Turning off screen updating is the way to achieve this  it also has performance improvements  significant if you are doing looping while switching around worksheets   workbooks    The command to do this is       Application ScreenUpdating   False   Don t forget to turn it back to True when your macros is finished

[vba] How to copy data from another workbook (excel)?

Examples related to vba

Examples related to excel