VBA Question: Need to copy multiple similarly named sheet from multiple Workbooks into one file.

[danielpsu]

Hi all, I’m looking for help with a script that can help my daily work functions. I get a folder with multiple PDF and .xls/.xlsm files in it and need to individually go through each file and open a specific tab named “AandB” within each file and copy sheet after sheet within these files into one separate master excel sheet. Looking for assistance to build a VBA script that will be able to open and import all the “AandB” sheets from about 40 separate excel files into one master sheet, while ignoring the PDF files within that folder.

Thank you in advance!

 

[NdNoviceHlp]

Hi danielpsu. U can trial this code. It places the "AandB" in the open workbook running the "Test sub". HTH. Dave

Private Sub test()
Dim FSO As Object, FolDir As Object, FileNm As Object
Dim TargetFolder As FileDialog, sht As Worksheet
'On Error GoTo erfix
Set TargetFolder = Application.FileDialog(msoFileDialogFolderPicker)
With TargetFolder
.AllowMultiSelect = False
.Title = "Select Folder:"
.Show
End With
If TargetFolder.SelectedItems.Count = 0 Then
MsgBox "PICK A Folder!"
Exit Sub
End If
Set FSO = CreateObject("scripting.filesystemobject")
Set FolDir = FSO.GetFolder(Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1))
Application.ScreenUpdating = False
For Each FileNm In FolDir.Files
If FileNm.Name Like "*" & ".xls" & "*" Then
Workbooks.Open FileName:=FileNm
For Each sht In Workbooks(FileNm.Name).Worksheets
If sht.Name = "AandB" Then
Workbooks(FileNm.Name).Sheets("AandB").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Exit For
End If
Next sht
Application.DisplayAlerts = False
Workbooks(FileNm.Name).Close SaveChanges:=False
End If
Next FileNm
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Set FolDir = Nothing
Set FSO = Nothing
Exit Sub
ErFix:
On Error GoTo 0
MsgBox "Error"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Set FolDir = Nothing
Set FSO = Nothing
End Sub

ps. If there are named ranges in the "AandB" sheet, a different approach may be needed.

 

[danielpsu]

Within each range of those excel files, I need to extract only certain data points if that's what you mean. To be specific, within each "AandB" sheet, I have certain rows that I need to include and certain rows that are unnecessary. All of the rows/columns match identically within those different Work files.

 

[NdNoviceHlp]

You're request was... "open a specific tab named “AandB” within each file and copy sheet after sheet within these files into one separate master excel sheet. Looking for assistance to build a VBA script that will be able to open and import all the “AandB” sheets from about 40 separate excel files into one". U never mentioned... "To be specific, within each "AandB" sheet, I have certain rows that I need to include and certain rows that are unnecessary".... THAT'S DIFFERENT! U will need to clearly indicate what row/columns from the AandB sheets U want to extract and exactly where U want to put them. I have provided the general outline for a solution perhaps others will assist U with the remaining part or maybe U could trial doing something for yourself. Dave
ps. No thank you for your time is necessary.