save as, closes master sheet then reopens the new saved worksheet

excelnoobhere

Board Regular
Joined
Mar 11, 2019
Messages
61
I have the Following button code that saves my workbook at a desired location (Save As) with a desired name without including the pages "Main" and template".

Right after I save the copy, I have another button that when clicked, it closes my "Master" workbook without any changes but keeps excel open without any sheets.

I'd like to be able to to click the close button (CloseWorkbook()) and automatically closes my "master" workbook and opens up the workbook I just saved. I need to be able to open it no matter where it was saved or any given name.

I'm guessing these have to be combined into one step

Thanx in advance

'save the folader at a desired location
Sub SaveWorkbook()


Dim ws As Worksheet
Dim savename As String
Dim cnt As Long
Dim arrSheets()


With ActiveWorkbook
ReDim arrSheets(1 To .Sheets.Count)
For Each ws In .Sheets
Select Case ws.Name
Case "Main", "template"
' do nothing
Case Else
cnt = cnt + 1
arrSheets(cnt) = ws.Name
End Select
Next ws
ReDim Preserve arrSheets(1 To cnt)
' copy sheets to new workbook
.Sheets(arrSheets).Copy
End With

MsgBox ("You will now be prompted to save your file, after naming the file click 'Save'") 'Notifies User
savename = Application.GetSaveAsFilename(fileFilter:="Exel Files (*.xlsx), *.xlsx")
If savename <> "False" Then
' save and close new workbook
With ActiveWorkbook
.SaveAs Filename:=savename, FileFormat:=51 'Something iswrong
.Close SaveChanges:=False
End With
End If




End Sub


Sub CloseWorkbook()
MsgBox ("Open the file that was just saved") 'Notifies User
ThisWorkbook.Close Saved = True


End Sub
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,512
Office Version
  1. 2013
Platform
  1. Windows
Code:
.Close SaveChanges:=False
If you comment this line out, or delete it, wouldn't that leave your new workbook open?
 
Last edited:

excelnoobhere

Board Regular
Joined
Mar 11, 2019
Messages
61
Code:
.Close SaveChanges:=False
If you comment this line out, or delete it, wouldn't that leave your new workbook open?

yes it will however, it does not close my "master" workbook without saving
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,112,816
Messages
5,542,660
Members
410,567
Latest member
SCraig123
Top