CODEWITHSUNDEEP
vbaexcel
Hashmat Habibzadah
Hashmat Habibzadah

Reputation: 45

How do I change my VBA code to only pull from sheets that have a specific name?

I found the below VBA code on a website. It essentially looks inside of a folder for excel spreadsheets and merges them into one. It only copies the information from the tab that was last open before saving and closing.

How do I change this code so that it only copies from a tab called "Template"?

Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")

'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("D:\change\to\excel\files\path\here")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3 
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate

'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub

Upvotes: 1

Views: 102

Answers (1)

user3598756
user3598756

Reputation: 29421

For Each everyObj In filesObj
    Set bookList = Workbooks.Open(everyObj)
    Worksheets("Template").Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy ThisWorkbook.Worksheets(1).Range("A65536").End(xlUp).Offset(1, 0)
    bookList.Close
Next

Or

For Each everyObj In filesObj
    With Workbooks.Open(everyObj).Worksheets("Template")
        .Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy ThisWorkbook.Worksheets(1).Range("A65536").End(xlUp).Offset(1, 0)
        .Close
    End With
Next

Upvotes: 1

Related Questions