Hello, I have written below code to automate the stacking of files saved in a specific folder. Now, I am trying to tweak the code to add the last report, ABC-4 to a different tab. Currently, its being stacked underneath the other 3. I also want to rename both tabs to "new name 1" and "new name 2".
Any help would be greatly appreciated!
Thanks,
Sub Stack_two_tabs()
Dim wbS As Workbook
Dim wbR As Workbook
Set wbS = Workbooks.Add
With wbS
Set wbR = Workbooks.Open("C:\Constr\ABC-1.xlsx")
wbR.Worksheets(1).UsedRange.Copy .Worksheets(1).Range("A1")
wbR.Close False
Set wbR = Workbooks.Open("C:\Constr\ABC-2.xlsx")
With .Worksheets(1)
wbR.Worksheets(1).UsedRange.Copy .Cells(.UsedRange.Cells(.UsedRange.Cells.Count).Row + 1, "A")
wbR.Close False
End With
Set wbR = Workbooks.Open("C:\Constr\ABC-3.xlsx")
With .Worksheets(1)
wbR.Worksheets(1).UsedRange.Copy .Cells(.UsedRange.Cells(.UsedRange.Cells.Count).Row + 1, "A")
wbR.Close False
End With
Sheets.Add After:=Sheets(Sheets.Count)
Set wbR = Workbooks.Open("C:\Constr\ABC-4.xlsx")
With .Worksheets(1)
wbR.Worksheets(1).UsedRange.Copy .Cells(.UsedRange.Cells(.UsedRange.Cells.Count).Row + 1, "A")
wbR.Close False
End With
.Title = "Stack with two tabs"
.SaveAs Filename:="Stack with two tabs.xlsx"
End With
End Sub
Bookmarks