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

danielpsu

New Member
Joined
Sep 8, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
3,168
Hi danielpsu. U can trial this code. It places the "AandB" in the open workbook running the "Test sub". HTH. Dave
Code:
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

New Member
Joined
Sep 8, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 9, 2002
Messages
3,168
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.
 

Forum statistics

Threads
1,181,218
Messages
5,928,749
Members
436,627
Latest member
caligirl626

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top