I would like to save my worksheet in the following manner: (Sheet1) Frontsheet (Sheet2) Asbestos Report (Sheet3) Civils (Sheet4) Cables
My code looks as follows:
With wkb
Application.DisplayAlerts = False
.SaveAs fileName:=PathName & Application.PathSeparator & fileName,
FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.DisplayAlerts = True
'----------------- Creating frontsheet
.Sheets("Sheet1").Name = "Frontsheet"
Set frontW = .Sheets("Frontsheet")
With frontW
End With
Set asbestW = wkb.Sheets.Add(After:= _
wkb.Sheets(ThisWorkbook.Sheets.Count))
asbestW.Name = "Asbestos Report"
With asbestW
Set LocW = wkb.Sheets.Add(After:= _
wkb.Sheets(ThisWorkbook.Sheets.Count))
LocW.Name = "Location Sheet"
End With
With LocW
Set CivW = wkb.Sheets.Add(After:= _
wkb.Sheets(ThisWorkbook.Sheets.Count))
CivW.Name = "Civils"
End With
With CivW
Set CabW = wkb.Sheets.Add(After:= _
wkb.Sheets(ThisWorkbook.Sheets.Count))
CabW.Name = "Cables"
End With
With CabW
End With
End With
Unfortunately, I have them in reverse sequence despite using the ThisWorkbook.Sheets.Count option like explained here:
How to add a named sheet at the end of all Excel sheets?
How could I sort this out?
