I have a macro written in Excel 2007 that looks in the same directory as my open workbook and copies the worksheets from all of the other files in the same directory. At least, that's what it is supposed to do.
The problem I am having is that when I first open my .xlsm worksheet and run that macro, it pulls worksheets from who knows where. If I do a Save As first, then run the macro, it works fine and pulls from the same directory as needed. It would be nice to have a cleaner macro that doesn't require Save As.
As a side note, I had it pointing to the generic "CurrentDirectory" because in the future, this file will be shared with others as a tool to use for reporting. Thus, the file path will change because we each have a unique 6 character ID. Saving the folder on my desktop has the path C:\users\LISA12\Desktop\MSW_ReportTool". If I give it to Joe to save on his desktop, then his path will be C:\users\JOE123\Desktop\MSW_ReportTool.
Here's what I do have:
Sub ImportReports()
Dim directory As String, fileName As String, sheet As Worksheet, total As Integer
'Turn off screen updating and displaying alerts
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Use Dir function to find the first *.xl?? file stored in the directory
directory = CurrentDirectory
fileName = Dir(directory & "*.xlsx")
'Copy each file worksheet, close the file, and move to the next one.
Do While fileName <> ""
Workbooks.Open (directory & fileName)
For Each sheet In Workbooks(fileName).Worksheets
total = Workbooks("MSW_ReportTool_v1.0.xlsm").Worksheets.Count
Workbooks(fileName).Worksheets(sheet.Name).Copy _
after:=Workbooks("MSW_ReportTool_v1.0.xlsm").Worksheets(total)
Next sheet
Workbooks(fileName).Close
fileName = Dir()
Loop
'Turn on screen updating and displaying alerts
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
The problem I am having is that when I first open my .xlsm worksheet and run that macro, it pulls worksheets from who knows where. If I do a Save As first, then run the macro, it works fine and pulls from the same directory as needed. It would be nice to have a cleaner macro that doesn't require Save As.
As a side note, I had it pointing to the generic "CurrentDirectory" because in the future, this file will be shared with others as a tool to use for reporting. Thus, the file path will change because we each have a unique 6 character ID. Saving the folder on my desktop has the path C:\users\LISA12\Desktop\MSW_ReportTool". If I give it to Joe to save on his desktop, then his path will be C:\users\JOE123\Desktop\MSW_ReportTool.
Here's what I do have:
Sub ImportReports()
Dim directory As String, fileName As String, sheet As Worksheet, total As Integer
'Turn off screen updating and displaying alerts
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Use Dir function to find the first *.xl?? file stored in the directory
directory = CurrentDirectory
fileName = Dir(directory & "*.xlsx")
'Copy each file worksheet, close the file, and move to the next one.
Do While fileName <> ""
Workbooks.Open (directory & fileName)
For Each sheet In Workbooks(fileName).Worksheets
total = Workbooks("MSW_ReportTool_v1.0.xlsm").Worksheets.Count
Workbooks(fileName).Worksheets(sheet.Name).Copy _
after:=Workbooks("MSW_ReportTool_v1.0.xlsm").Worksheets(total)
Next sheet
Workbooks(fileName).Close
fileName = Dir()
Loop
'Turn on screen updating and displaying alerts
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
