Sub Copy_WB
Dim wbReport As Workbook
'This copies all the worksheets from your template into report
‘Worksheets are put in an array, you can add as many as you need
Worksheets(Array("First WS", "Second WS", "Third WS", "Fourth WS")).Copy
'Saves a local copy of your WB.
‘This also renames the file with current date.
ActiveWorkbook.SaveAs Filename:="Your workbook name here " & Format(Date, "Long Date") & ".xlsx", FileFormat:=51
'we want to make sure that we are working with the right workbook
‘This alse saves the copy as a macro-enabled workbook
Set wbReport = Workbooks.Open("Your workbook name here " & Format(Date, "Long Date") & ".xlsx")
‘Define and set worksheets
Dim firstWS As Worksheet
Set firstWS = wbReport.Worksheets("First WS")
Dim secondWS As Worksheet
Set secondWS = wbReport.Worksheets("Second WS")
Dim thirdWS As Worksheet
Set thirdWS = wbReport.Worksheets("Third WS")
Dim fourthWS As Worksheet
Set fourthWS = wbReport.Worksheets("Fourth WS")
‘Let’s REALLY hide the data in the fourth worksheet using this code, ensuring users are unable
‘Unable to unhide it
wbReport.Sheets("Fourth WS").Visible = xlSheetVeryHidden
End Sub
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)