Working with a macro-enabled template that is made up of 8 sheets:
Sheet1(Common data) - contains data that is common across Sheets2-4. This sheet is xlveryhidden as the users don't need to view it or modify it.
Sheet2(Report template - Advanced)
Sheet3(Report template - NextGen)
Sheet4(Report template - All Future)
Sheet5(Enable Content Prompt) - shows a prompt to the user to remind them, if necessary, to 'Enable Content' in the Security Warning bar.
Sheet6(Instructions - NextGen)
Sheet7(Instructions - Advanced)
Sheet8(Instructions - All Future)
A User Form appears when the template is opened and allows for the selection of the correct department / section.
If, for example, 'Advanced' is selected then Sheet7(Instructions - Advanced) and Sheet2(Report Template - Advanced) will become visible to the user while the remaining sheets will become/remain hidden/very hidden.
Once the user has filled in the report template then they have two ways of doing a 'saveas'. One is via the normal Ctrl+S and the other is via a Comdbutton within the template.
Ctrl+S triggers the following macro:
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim xFileName As String
Dim L5part As String, H17part As String
Dim ws As Worksheet
If ThisWorkbook.Worksheets("Report template - Advanced").Visible = True Then
Set ws = ThisWorkbook.Worksheets("Report template - Advanced")
ElseIf ThisWorkbook.Worksheets("Report template - NextGen").Visible = True Then
Set ws = ThisWorkbook.Worksheets("Report template - NextGen")
Else
Set ws = ThisWorkbook.Worksheets("Report template - All Future")
End If
L5part = ws.Range("L5").Value
L5part = Replace(Replace(L5part, " / ", "/"), "/", "_")
'MsgBox L5part
H17part = ws.Range("H17").Value
H17part = Replace(H17part, "/", "_")
'MsgBox H17part
If SaveAsUI <> False Then
Cancel = True
xFileName = Application.GetSaveAsFilename(H17part & " Report" & ws.Range("A1").Value & L5part, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save report as")
If xFileName <> "False" Then
Application.EnableEvents = False
ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.EnableEvents = True
End If
End If
End Sub
whilst the Commandbutton triggers the following macro:
Code:
Private Sub CommandButton1_Click()
Sheet2.CommandButton1.BackColor = RGB(242, 242, 242)
Dim fName As Variant
Dim L5part As String, H17part As String
L5part = ThisWorkbook.Worksheets("Report template - Advanced").Range("L5").Value
L5part = Replace(Replace(L5part, " / ", "/"), "/", "_")
H17part = ThisWorkbook.Worksheets("Report template - Advanced").Range("H17").Value
H17part = Replace(H17part, "/", "_")
fName = Application.GetSaveAsFilename(InitialFileName:=H17part & " CI Report" & Range("A1").Value & L5part, FileFilter:="Excel Files (*.xlsm), *.xlsm", Title:="Save As report as")
If fName = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=52
End Sub
The issue that I am having is that I only want the two visible sheets, in this case, Sheet2 and Sheet7 (both visible) along with Sheet1 (veryhidden) and Sheet5 (hidden) to be copied / exported into the new .xlsm workbook. I don't need the UserForm or the other Sheets copied / exported over.
How do I do that? What if it's Sheet3 and Sheet6 or Sheet4 and Sheet8 that are the visible sheets?
I have googled and found code to export the visible sheets (not that I have been able to get that to successfully work
) but haven't been able to find anything that allows for the export of visible sheets plus specified others...
Sheet1 is important insofar as it contains the common data so if the user needs to make a change to the .xlsm workbook then they are able to and Sheet5 is important insofar as reminding the user to, if necessary, click the 'Enable Content' button to make the macros work.
Credit goes to 'NoSparks' for a lot of the code and assistance he has provided me up to date.
Many thanks
Mike