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
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