Hello,
I'm working on the below code which right now grabs all sheets named AP35 from workbooks in a specified folder. There's two ways I want to alter this but I haven't figured out a way:
1. Instead of referencing an exact link in the code, can I have it reference a cell (I2) every month instead of having to update the code each month with the new location? It'll change quarterly.
* This line in the below code. (fd.InitialFileName = "C:\folder" ) I'm sure this part is easy!
2. The sheet names are all AP35, AP35(1), AP35(2), etc. Can I redo this in a way that will name the sheets after the file name instead? I'd love for it to match the first half of the name of the workbooks it pulls the sheet from. They all end with " Approval File", so if there's a way to have it use the original file name minus that, I'd like to know. Unfortunately, the file names have a different number of characters before that. This is my code so far:
Sub Test()
Dim fd As FileDialog
Dim FilePicked As Integer, f As Integer
Dim sWb As Workbook
Dim ws As Worksheet
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.InitialFileName = "C:\folder"
fd.AllowMultiSelect = True
FilePicked = fd.Show
Application.ScreenUpdating = False
If FilePicked = 0 Then
Application.ScreenUpdating = True
Exit Sub
Else
For f = 1 To fd.SelectedItems.Count
Set sWb = Workbooks.Open(fd.SelectedItems(f))
For Each ws In sWb.Worksheets
If ws.Name = "AP35" Then
ws.Copy _
After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
End If
Next ws
sWb.Close False
Next f
End If
Application.ScreenUpdating = True
End Sub
TIA!
Rachel
I'm working on the below code which right now grabs all sheets named AP35 from workbooks in a specified folder. There's two ways I want to alter this but I haven't figured out a way:
1. Instead of referencing an exact link in the code, can I have it reference a cell (I2) every month instead of having to update the code each month with the new location? It'll change quarterly.
* This line in the below code. (fd.InitialFileName = "C:\folder" ) I'm sure this part is easy!
2. The sheet names are all AP35, AP35(1), AP35(2), etc. Can I redo this in a way that will name the sheets after the file name instead? I'd love for it to match the first half of the name of the workbooks it pulls the sheet from. They all end with " Approval File", so if there's a way to have it use the original file name minus that, I'd like to know. Unfortunately, the file names have a different number of characters before that. This is my code so far:
Sub Test()
Dim fd As FileDialog
Dim FilePicked As Integer, f As Integer
Dim sWb As Workbook
Dim ws As Worksheet
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.InitialFileName = "C:\folder"
fd.AllowMultiSelect = True
FilePicked = fd.Show
Application.ScreenUpdating = False
If FilePicked = 0 Then
Application.ScreenUpdating = True
Exit Sub
Else
For f = 1 To fd.SelectedItems.Count
Set sWb = Workbooks.Open(fd.SelectedItems(f))
For Each ws In sWb.Worksheets
If ws.Name = "AP35" Then
ws.Copy _
After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
End If
Next ws
sWb.Close False
Next f
End If
Application.ScreenUpdating = True
End Sub
TIA!
Rachel