One veryhidden sheet, one hidden sheet and two visible sheets

kiwimtnbkr · #1 06-22-2019, 11:31 PM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to map number of sheets one line per sheet into one sheet	abbani	Excel	3	12-12-2016 04:10 AM
Can a Data Source be one sheet in a multi-sheet Workbook	nfotx	Mail Merge	1	07-01-2015 12:55 AM
Workbook with daily active sheet	MDMeyers	Excel	1	01-10-2015 04:48 AM
permanently unprotecting a sheet or workbook	mrmagoo	Excel	2	07-04-2012 08:12 PM
How do I merge data from one sheet in a workbook out into multiple sheets	nolesca	Excel	4	06-07-2010 08:13 AM