Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-22-2019, 11:31 PM
kiwimtnbkr kiwimtnbkr is offline One veryhidden sheet, one hidden sheet and two visible sheets - how to saveas into .xlsm workbook Windows 10 One veryhidden sheet, one hidden sheet and two visible sheets - how to saveas into .xlsm workbook Office 2010 64bit
Advanced Beginner
One veryhidden sheet, one hidden sheet and two visible sheets - how to saveas into .xlsm workbook
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default 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
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
Reply With Quote
  #2  
Old 06-25-2019, 08:13 PM
NoSparks NoSparks is offline One veryhidden sheet, one hidden sheet and two visible sheets - how to saveas into .xlsm workbook Windows 7 64bit One veryhidden sheet, one hidden sheet and two visible sheets - how to saveas into .xlsm workbook Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Care to post the workbook ?
Reply With Quote
  #3  
Old 06-26-2019, 02:08 AM
kiwimtnbkr kiwimtnbkr is offline One veryhidden sheet, one hidden sheet and two visible sheets - how to saveas into .xlsm workbook Windows 10 One veryhidden sheet, one hidden sheet and two visible sheets - how to saveas into .xlsm workbook Office 2010 64bit
Advanced Beginner
One veryhidden sheet, one hidden sheet and two visible sheets - how to saveas into .xlsm workbook
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

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
Attached Files
File Type: xltm Report Template - for upload.xltm (296.1 KB, 6 views)
Reply With Quote
  #4  
Old 06-26-2019, 08:15 AM
NoSparks NoSparks is offline One veryhidden sheet, one hidden sheet and two visible sheets - how to saveas into .xlsm workbook Windows 7 64bit One veryhidden sheet, one hidden sheet and two visible sheets - how to saveas into .xlsm workbook Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Sorry, not able to assist you with this file.
Reply With Quote
  #5  
Old 06-26-2019, 11:30 AM
kiwimtnbkr kiwimtnbkr is offline One veryhidden sheet, one hidden sheet and two visible sheets - how to saveas into .xlsm workbook Windows 10 One veryhidden sheet, one hidden sheet and two visible sheets - how to saveas into .xlsm workbook Office 2010 64bit
Advanced Beginner
One veryhidden sheet, one hidden sheet and two visible sheets - how to saveas into .xlsm workbook
 
Join Date: Oct 2017
Posts: 69
kiwimtnbkr is on a distinguished road
Default

Quote:
Originally Posted by NoSparks View Post
Sorry, not able to assist you with this file.
Many thanks for taking a look - your time and assistance is very much appreciated.

cheers
Mike
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
One veryhidden sheet, one hidden sheet and two visible sheets - how to saveas into .xlsm workbook How to map number of sheets one line per sheet into one sheet abbani Excel 3 12-12-2016 04:10 AM
One veryhidden sheet, one hidden sheet and two visible sheets - how to saveas into .xlsm workbook 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
One veryhidden sheet, one hidden sheet and two visible sheets - how to saveas into .xlsm workbook permanently unprotecting a sheet or workbook mrmagoo Excel 2 07-04-2012 08:12 PM
One veryhidden sheet, one hidden sheet and two visible sheets - how to saveas into .xlsm workbook How do I merge data from one sheet in a workbook out into multiple sheets nolesca Excel 4 06-07-2010 08:13 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:30 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft