Copy last row of data in multiple files to a new file

[Callie]

Hello,

The code below works perfectly to copy the last row of data from multiple files under one folder to a single sheet in a new file, but my source files have several tabs.

I am unsure where to specify the name of the sheet that I want to copy from. In my particular case, it will always be the same name, FYVariance, for example.

Thank you in advance for suggestions to solve this.

Sub merched() 
Dim SrcBook As Workbook 
Dim fso As Object, f As Object, ff As Object, f1 As Object 

Application.ScreenUpdating = False 
Set fso = CreateObject("Scripting.FileSystemObject") 
Set f = fso.Getfolder("D:\Temp\") 
Set ff = f.Files 

For Each f1 In ff 
Set SrcBook = Workbooks.Open(f1) 
Range("E8:IV" & Range("E65536").End(xlUp).Row).Copy 
ThisWorkbook.Worksheets(1).Activate 
Range("E65536").End(xlUp).Offset(1, 0).PasteSpecial 
Application.CutCopyMode = False 
SrcBook.Close 
Next 
End Sub

 

[GraH - Guido]

between these 2 lines

Set SrcBook = Workbooks.Open(f1) 
Range("E8:IV" & Range("E65536").End(xlUp).Row).Copy

insert something like

f1.worksheets("FYVariance").activate

 

[Belleke]

Avoid using activate and/or select. (it makes the code slower and it is rarely necessary)

Sheets("FYVariance").Range("E8:IV" & Range("E65536").End(xlUp).Row).Copy

and

ThisWorkbook.Worksheets(1).Range("E65536").End(xlUp).Offset(1, 0).PasteSpecial

 

[Callie]

Hello,

Sorry for the delay in replying. New job is taking up all of my time, but this macro will really help. The code now looks like this and seems to work perfectly.

Sub merched()
Dim SrcBook As Workbook
Dim fso As Object, f As Object, ff As Object, f1 As Object
Application.ScreenUpdating = False
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.Getfolder("D:\Temp\")
Set ff = f.Files
For Each f1 In ff
Set SrcBook = Workbooks.Open(f1)
Sheets("FYVariance").Range("E8:IV" & Range("E65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
ThisWorkbook.Worksheets(1).Range("E65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
SrcBook.Close
Next
End Sub

Many thanks. I will definitely continue to use this board and make a contribution in appreciation for all of the help.

 

[Belleke]

Hi Callie, thanks for the feedback
But you do not need this line of code ThisWorkbook.Worksheets(1).Activate, just delete it.