Greetings, I have the below code that works well enough, however I would like to limit it to import only one specific Sheet from each of the files it is querying. The files this imports has multiple sheets in each workbook. I would like to limit to a sheet Named "PBR". each workbook will have a sheet named that.
Sub InportSheets()
Dim directory As String, fileName As String, sheet As Worksheet, total As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
directory = "C:\Users\xxx\Downloads\__PBRs for Test Load\"
fileName = Dir(directory & "*.xl??")
Do While fileName <> ""
Workbooks.Open (directory & fileName)
For Each sheet In Workbooks(fileName).Worksheets
total = Workbooks("import-sheets.xlsm").Worksheets.Count
Workbooks(fileName).Worksheets(sheet.Name).Copy _
after:=Workbooks("import-sheets.xlsm").Worksheets(total)
Next sheet
Workbooks(fileName).Close
fileName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Sub ImportPBR()
Dim directory As String
Dim fileName As String
Dim wbkS As Workbook
Dim wbkT As Workbook
Dim sheet As Worksheet
Dim total As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wbkT = Workbooks("import-sheets.xlsm")
directory = "C:\Users\xxx\Downloads\__PBRs for Test Load\"
fileName = Dir(directory & "*.xl??")
Do While fileName <> ""
Set wbkS = Workbooks.Open(directory & fileName)
Set sheet = wbkS.Worksheets("PBR")
total = wbkT.Worksheets.Count
sheet.Copy After:=wbkT.Worksheets(total)
wbkS.Close SaveChanges:=False
fileName = Dir
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Some of the workbooks will have "PBR" as the target sheet name, and others will be "Sheet1" I am wondering if there is a way to handle that in the code for selecting the target sheet to grab the data from.
Sub ImportPBR()
Dim directory As String
Dim fileName As String
Dim wbkS As Workbook
Dim wbkT As Workbook
Dim sheet As Worksheet
Dim total As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wbkT = Workbooks("import-sheets.xlsm")
directory = "C:\Users\xxx\Downloads\__PBRs for Test Load\"
fileName = Dir(directory & "*.xl??")
Do While fileName <> ""
Set wbkS = Workbooks.Open(directory & fileName)
Set sheet = Nothing
On Error Resume Next
Set sheet = wbkS.Worksheets("PBR")
If sheet Is Nothing Then
Set sheet = wbkS.Worksheets("Sheet1")
If sheet Is Nothing Then
MsgBox "PBR nor Sheet1 found!", vbExclamation
End If
End If
total = wbkT.Worksheets.Count
sheet.Copy After:=wbkT.Worksheets(total)
On Error GoTo 0
wbkS.Close SaveChanges:=False
fileName = Dir
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub