|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
One veryhidden sheet, one hidden sheet and two visible sheets - how to saveas into .xlsm workbook
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 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 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 |
#2
|
|||
|
|||
Care to post the workbook ?
|
#3
|
|||
|
|||
hi NoSparks,
See the attached. If my original request is looking like it's going to be a ton of work, or just not possible, then would it be possible to stop the UserForm initializing if it 'saw' that the file type that was trying to opened was .xlsm? Initialize the UserForm if .xltm - don't initialize the UserForm if .xlsm? Note that all the sheets are currently 'protected' and the current password is 'A'. Many thanks Mike |
#4
|
|||
|
|||
Sorry, not able to assist you with this file.
|
#5
|
|||
|
|||
Many thanks for taking a look - your time and assistance is very much appreciated.
cheers 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 |