Hi,
I posted earlier, but i have managed to get somewhere. I am using the below code to save a master spreadsheet (holiday planner) in another location however, it is only saving the one sheet i have specified but i want it to save all of the sheets. i don't know how to amend the code to save them all.
I can't save the whole workbook as a copy of as I have created the templates for where the "copy" saves as I don't want staff to see certain rows so have hidden these.
This is the code -
Sub beforesave()
Dim planWbk As Workbook
Dim planner As Workbook
Application.ScreenUpdating = False
Application.EnableEvents = True
Set planWbk = ActiveWorkbook
Workbooks.Open "\\Customer\shared\Exeter\Revenue Management\B2C Annual Leave\Martin Jose Planner.xls", , , , , "testing123"
Set planner = ActiveWorkbook
planner.Sheets("Inbound South - Plymouth").Activate
planner.Sheets("Inbound South - Plymouth").Unprotect
planWbk.Activate
Sheets("Inbound South - Plymouth").Activate
ActiveSheet.Unprotect
Cells.Select
Application.CutCopyMode = False
Selection.Copy
planner.Activate
Sheets("Inbound South - Plymouth").Select
Range("a1").PasteSpecial xlPasteValues
ActiveSheet.Protect
planner.Close True
planWbk.Activate
Sheets("Inbound South - Plymouth").Activate
Application.ScreenUpdating = True
Thanks in advance.
I posted earlier, but i have managed to get somewhere. I am using the below code to save a master spreadsheet (holiday planner) in another location however, it is only saving the one sheet i have specified but i want it to save all of the sheets. i don't know how to amend the code to save them all.
I can't save the whole workbook as a copy of as I have created the templates for where the "copy" saves as I don't want staff to see certain rows so have hidden these.
This is the code -
Sub beforesave()
Dim planWbk As Workbook
Dim planner As Workbook
Application.ScreenUpdating = False
Application.EnableEvents = True
Set planWbk = ActiveWorkbook
Workbooks.Open "\\Customer\shared\Exeter\Revenue Management\B2C Annual Leave\Martin Jose Planner.xls", , , , , "testing123"
Set planner = ActiveWorkbook
planner.Sheets("Inbound South - Plymouth").Activate
planner.Sheets("Inbound South - Plymouth").Unprotect
planWbk.Activate
Sheets("Inbound South - Plymouth").Activate
ActiveSheet.Unprotect
Cells.Select
Application.CutCopyMode = False
Selection.Copy
planner.Activate
Sheets("Inbound South - Plymouth").Select
Range("a1").PasteSpecial xlPasteValues
ActiveSheet.Protect
planner.Close True
planWbk.Activate
Sheets("Inbound South - Plymouth").Activate
Application.ScreenUpdating = True
Thanks in advance.