Hi!
I'm trying to tweak an old macro I have to use for a new process.
Basically, I have several client reports which each have a "Summary" sheet in them. I'd like this macro to combine all of the summary sheets into one workbook (where the macro will be run from) and name them "Payee ID", " ", "Summary". The payee ID is found in cell B11 of each summary sheet. I know it's not set up at all like that but it's still based on the old one and I haven't been able to get it right.
So in the code below, the "C4" value is where the path to all the files is pasted. Right now when I run the macro, it opens a window to that path but it doesn't actually run the macro so I'm stuck.
Basically the two things I'm stumped on:
1. Getting the renaming of the sheets correct
2. Getting the macro to actually run and not just open a window to the file path.
Any suggestions? Let me know if I'm not explaining this well![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
I'm trying to tweak an old macro I have to use for a new process.
Basically, I have several client reports which each have a "Summary" sheet in them. I'd like this macro to combine all of the summary sheets into one workbook (where the macro will be run from) and name them "Payee ID", " ", "Summary". The payee ID is found in cell B11 of each summary sheet. I know it's not set up at all like that but it's still based on the old one and I haven't been able to get it right.
So in the code below, the "C4" value is where the path to all the files is pasted. Right now when I run the macro, it opens a window to that path but it doesn't actually run the macro so I'm stuck.
Basically the two things I'm stumped on:
1. Getting the renaming of the sheets correct
2. Getting the macro to actually run and not just open a window to the file path.
Any suggestions? Let me know if I'm not explaining this well
Code:
Sub GatherSummaries()
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 = ActiveSheet.Range("C4").Value
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 = "Summary" Then
ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
Sheets(Sheets.Count).Name = Left(sWb.Name, InStr(sWb.Name, " Summary") - 1)
End If
Next ws
sWb.Close False
Next f
End If
Application.ScreenUpdating = True