PDA

View Full Version : [SOLVED] Pull data from specific worksheet from multiple workbooks



mirat1687
04-25-2017, 01:35 AM
I am a newbie in VBA.

My concern is that I wish to pull all data from a specific worksheet from all workbooks from a particular folder. All workbooks have 3 worksheets from which I need to pull data of 1 specific worksheet.

I have written below code which pulls all data from all worksheets. Can anyone help to modify to pull data from a specific worksheet say "Sheet1".


Sub ConslidateWorkbooks()
'Code created by Mirat Shah
Dim FolderPath As String
Dim Filename As String
Dim Sheet As Worksheet
Application.ScreenUpdating = False
FolderPath = Environ("userprofile") & "\Desktop\RPT\"
Filename = Dir(FolderPath & "*.xls*")
Do While Filename <> ""
Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
Application.ScreenUpdating = True
End Sub

mdmackillop
04-25-2017, 01:45 AM
Try this
I added a couple of variables for clarity

Sub ConslidateWorkbooks()
'Code created by Mirat Shah
Dim FolderPath As String
Dim Filename As String
Dim Sheet As Worksheet
Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim i as long

Application.ScreenUpdating = False
Set wbTarget = ThisWorkbook
FolderPath = Environ("userprofile") & "\Desktop\RPT\"
Filename = Dir(FolderPath & "*.xls*")
Do While Filename <> ""
Set wbSource = Workbooks.Open(Filename:=FolderPath & Filename, ReadOnly:=True)
i=i+1 'if display order matters
wbSource.Sheets("Sheet1").Copy After:=wbTarget.Sheets(i)
wbSource.Close False
Filename = Dir()
Loop
Application.ScreenUpdating = True
End Sub


PS I've delete your appended question to the old posting.

mirat1687
04-26-2017, 09:32 AM
Thanks a lot, that worked exactly how I wanted it.