Hi there. Help needed please. TIA!
I'm trying to write a code that will take select sheets (not all sheets) from multiple files and move them all into one workbook. I have 10 files and I want to pull certain sheets from each file and combine them into one workbook. The problem I'm having is that its taking all the sheets within each file and moving them to seperate files. Here's the code I'm using:
Sub Publish_Reports()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Dim wb As Workbook
Set wb = Workbooks.Add
Store = Left(ws.Name, 5)
Path = "Folder Path" & Store & ".xls"
wb.SaveAs Path, FileFormat:=56
ws.Copy Before:=Workbooks(wb.Name).Sheets(1)
wb.Save
wb.Close
Next
End Sub
Where did I go wrong?
Bookmarks