VBA to import Sheets from other workbooks

[celias]

Hi! Could any of you please help me with this?

I have a file - GetData - where I would like to run a macro that would go to a folder (has 2 files), and for each file it:
copies sheet1 and pastes it into my GetDatafile.

I would also like that this new information on GetData file would replace previous one, if any.

This is the code that I have so far:

Sub ImportData()
'
'ImportData Macro
'First, we declare two variables of type String, a Worksheet object and one variable of type Integer.
Dim directory As String, fileName As String, sheet As Worksheet, total As Integer

' Turn off screen updating and displaying alerts.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
' Initialize the variable directory. We use the Dir function to find the first *.xl?? file stored in this directory.
directory = "I:\Data\Reports\"
fileName = Dir(directory & "*.xl??")

' The variable fileName now holds the name of the first Excel file found in the directory. Add a Do While Loop.
Do While fileName <> ""

        ' There is no simple way to copy worksheets from closed Excel files. Therefore we open the Excel file.
        Workbooks.Open (directory & fileName)
        
        ' Import the sheets from the Excel file into import-sheet.xls.
        
        For Each sheet In Workbooks(fileName).Worksheets
             'total = Workbooks("GetData.xls").Worksheets.Count
             Workbooks(fileName).Worksheets(sheet.Name).Copy _
             'after:=Workbooks("GetData.xls").Worksheets(total)
        Next sheet
        'Explanation: the variable total holds track of the total number of worksheets of Donors_TaxReceipts.xls.
        'We use the Copy method of the Worksheet object to copy each worksheet and paste it after the last worksheet of import-sheets.xls.
        
        'Close the Excel file.
        Workbooks(fileName).Close
        
        'The Dir function is a special function. To get the other Excel files, you can use the Dir function again with no arguments.
        fileName = Dir()
        
        'Note: When no more file names match, the Dir function returns a zero-length string ("").
        'As a result, Excel VBA will leave the Do While loop.

Loop

'Turn on screen updating and displaying alerts again (outside the loop).
Application.ScreenUpdating = True
Application.DisplayAlerts = True
 
End Sub

Problem:
it opens the files but creates new workbooks instead of inserting the copied info into my workbook GetData.


Thank you in advance for any help.

 

[JLGWhiz]

Workbooks(fileName).Worksheets(sheet.Name).Copy _
 'after:=Workbooks("GetData.xls").Worksheets(total)

the apostrophe needs to be removed if you want the copied sheet to be inserted into the GetData file. The apostrophe makes that part of the statement a comment instead of an instruction to the compiler.

 

[celias]

Of course, I forgot to take that out after an experience. If I keep that, I get an error message "Run-time error '9': Subscript out of range" on line 24

 total = Workbooks("Donors_TaxReceipts.xls").Worksheets.Count

When hovering the mouse over this line it says that the value is 0. But all the workbooks have non empty sheet.

Any suggestion?

Thanks.

 

[dchaney]

Are you truly saving these files as .xls files or are they .xlsx or .xlsm?

 

[celias]

GetData is .xlsm as this is the file with the macro to run.
The other files are .xlsx.

 

[celias]

Oh! Now I see... I changed that and I it is working!!! YES!
Thank so much for your help. You made my day!

 

[dchaney]

My Pleasure