VBA to save as 'Excel Workbook' (not Macro enabled)

[bakarken]

Hi guys

As always, I really appreciate any help received here you guys are fantastic.

I'm using Excel 2019 and I'd like to create a VBA that works when a Developer button is pressed.

A user will open the Macro Enabled workbook 'template spreadsheet' but I'd like a button present on the 'Setup' tab that, when pressed, will do the following:
- Save as, with type 'Excel Workbook' (not Macro Enabled, I'm aware that macros after this save will break which I am fine with)
- File name to be whatever is written in cell A1 (using formula)
- The user then chooses the location save location theselves, and clicks save
- Once location chosen and file saved as Excel workbook, the original 'Setup' tab is hidden (not deleted)

Is this possible at all?
I'm happy to have a second button for the 4th step above, which would be easy to record I'm sure.

Thanks in advance!!

 

[frabulator]

Is this possible at all?

You would be surprised what you can do with the 'record macro' button inside the Developer tab.

Enable the developer tab (Excel> Options> Customize Ribbon> Developer) and record yourself doing the steps you listed. Everything (except for step 4) can be recorded.

For step 4, look at this link.

Get some code together and if you need help let us know. Having something to work off of allows us to help you a lot easier!

 

[bakarken]

You would be surprised what you can do with the 'record macro' button inside the Developer tab.

Enable the developer tab (Excel> Options> Customize Ribbon> Developer) and record yourself doing the steps you listed. Everything (except for step 4) can be recorded.

For step 4, look at this link.

Get some code together and if you need help let us know. Having something to work off of allows us to help you a lot easier!

Hi frabulator

Thanks for getting back to me.

I've tried to record, then clicking File, Save As, change type to Excel Workbook, then stopped recording but no code appears except from Buttonclick/End sub - am I missing something?
With step 4, sorry for being unclear, the user will manually choose the save file location but it is more that I want the 'Setup' tab hidden once saved

 

[frabulator]

I've tried to record, then clicking File, Save As, change type to Excel Workbook, then stopped recording but no code appears except from Buttonclick/End sub - am I missing something?

Possibly???

I tried the same think you explained and the recording generated the following:

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveWorkbook.SaveAs Filename:="C:\Users\GRAY\Documents\Book2.xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

When you click record, do exactly what you want Excel to do. Do not click any extra buttons, or run custom macros.

Click 'Record' and then go to File> Save As> (choose a location - change the file type to .xlsm) > Save

and the macro will record exactly what is above.

You can do this will all your steps and then combine them together to make your macro.

With step 4, sorry for being unclear, the user will manually choose the save file location but it is more that I want the 'Setup' tab hidden once saved

Sorry, that was a typo on my part. I meant to include that link for step 3, with the custom 'Choose your own save location' dialog box option.

You can easily hide a tab by doing the same thing with the recorder.

To produce the following code I clicked "record", right clicked on the sheet tab name in question, and selected 'hide' from the menu, then stopped the recording.

Sub Macro2()
'
' Macro2 Macro
'

'
    Sheets("Sheet2").Select
    ActiveWindow.SelectedSheets.Visible = False
End Sub

Hope that helps!

 

[frabulator]

Also, it is a helpful tip to learn how to search for VBA code.

VBA is not only Excel, in fact, Excel has its own custom version of Visual Basic that shares the same name as the standard VBA.

When looking up how to do something, say with Google, it will always give the best results if you include 'VBA Excel' before your search.

VBA Excel hide tabs
VBA Excel lock cells

~Hope that helps!