Excel macro. Record macro to save spreadsheet with unique name, using on a list of 20 individual spreadsheets in single folder.
I am an experienced Excel user who has never recorded a macro. I've always followed a very intense click sequence to format and save 20 individual spreadsheets each week..
I'm trying to use the Record Macro function but have a problem. Once I record a macro to save and try to run it on the next spreadsheet it renames the 2nd sheet with the name on the first sheet when the record button was on. I want to name it to the name of the current sheet I'm running the save macro on.
I've never used VBA coding, and trying to avoid it. I simply want each sheet to be saved with the name it had when opened after formatting..
Example:1st sheet name 6 14 21 AC 214 972 469.(recorded macro using this spreadsheet)
2nd sheet name 6 14 21 AC 281 713 832. Run save macro and it's renamed to AC 214 972 499
and all 20 following spreadsheets.
I'm trying to use the Record Macro function but have a problem. Once I record a macro to save and try to run it on the next spreadsheet it renames the 2nd sheet with the name on the first sheet when the record button was on. I want to name it to the name of the current sheet I'm running the save macro on.
I've never used VBA coding, and trying to avoid it. I simply want each sheet to be saved with the name it had when opened after formatting..
Example:1st sheet name 6 14 21 AC 214 972 469.(recorded macro using this spreadsheet)
2nd sheet name 6 14 21 AC 281 713 832. Run save macro and it's renamed to AC 214 972 499
and all 20 following spreadsheets.
ASKER
Bembi, Thanks for your help.
Here is the saved macro I recorded.
The Bold File Name is the file I used to record the macro.
A sample file is uploaded, named Test 7.xls. When I run this macro on test 7 I want it to save as "Test 7.xls"
I want the macro to save it to the name of the open file.
I usually have 20 more files just like this, each with a unique name.
Sub Save_1()
'
' Save_1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
ChDir "C:\Users\Dan\Desktop\SSS 2\Export Cust"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Dan\Desktop\SSS 2\Export Cust\06 14 21 test 1.xls", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub
06 14 21 test 7.xls
Here is the saved macro I recorded.
The Bold File Name is the file I used to record the macro.
When i run the recorded macro on the next file (Test 2.xls) it saves as Test 1.xls. I want it to save as Test 2.A sample file is uploaded, named Test 7.xls. When I run this macro on test 7 I want it to save as "Test 7.xls"
I want the macro to save it to the name of the open file.
I usually have 20 more files just like this, each with a unique name.
Sub Save_1()
'
' Save_1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
ChDir "C:\Users\Dan\Desktop\SSS 2\Export Cust"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Dan\Desktop\SSS 2\Export Cust\06 14 21 test 1.xls", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub
06 14 21 test 7.xls
I put the names (path and file) into variables, just easier to change something if needed, and thrown out unneeded options
Sub Save_1()
'
' Save_1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
Dim filename As String
Dim filepath As String
filepath = "C:\Users\Dan\Desktop\SSS 2\Export Cust\"
filename = ActiveSheet.Name & ".xls"
ActiveWorkbook.SaveAs filename:=filepath & filename, FileFormat:=xlExcel8
End SubASKER
Bambi, Thanks again for your help.
I'm having trouble testing your macro. I've copied it exactly, to my Personal.xlsb file and running it. It will not close the file in the .xls format. It gives me the message that it can't save in the .xlsx format and I have to manually go back and switch the format to .xls. Can we fix that?
My manual click path is "save as", click to change format to .xls, save , close file.
Thanks, Dan
I'm having trouble testing your macro. I've copied it exactly, to my Personal.xlsb file and running it. It will not close the file in the .xls format. It gives me the message that it can't save in the .xlsx format and I have to manually go back and switch the format to .xls. Can we fix that?
My manual click path is "save as", click to change format to .xls, save , close file.
Thanks, Dan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Bambi,
My apologies for being so slow in getting back. Sometimes life gets in the way of doing what I want to do.
I have been able to use your macro successfully. It's doing what I want successfully.What we have saves me a couple clickss times 20 spreadsheets every Monday.
I did forget one more step. When I run the macro a message pops up asking if I want to save "yes" or "no". The active is set to no and I have to switch to yes,. and then click X to close the worksheet. Two more clicks x 20 spreadsheets.Can we add that to the macro.
Thanks, It's a nice shortcut that saves me time. Dan
My apologies for being so slow in getting back. Sometimes life gets in the way of doing what I want to do.
I have been able to use your macro successfully. It's doing what I want successfully.What we have saves me a couple clickss times 20 spreadsheets every Monday.
I did forget one more step. When I run the macro a message pops up asking if I want to save "yes" or "no". The active is set to no and I have to switch to yes,. and then click X to close the worksheet. Two more clicks x 20 spreadsheets.Can we add that to the macro.
Thanks, It's a nice shortcut that saves me time. Dan
The message box possibly mean, that it ask if you wan tto run macros?
This you can change in the security settings.
Tools, Options, Trust Center, Click on Trust Center Settings...
I assume Macro Settings....
The second (closing Excel) you can realize by
ActiveWorkbook.Close
or
ActiveWorkbook.Close False (if you get another message)
als the last line
This you can change in the security settings.
Tools, Options, Trust Center, Click on Trust Center Settings...
I assume Macro Settings....
The second (closing Excel) you can realize by
ActiveWorkbook.Close
or
ActiveWorkbook.Close False (if you get another message)
als the last line
ASKER
Bembi, Thanks that did it. Dan
the macro recorder just is recording, what you do with the mouse.
It is neither optimal nor it can built up a logic.
But a good start point to recognize objects you need to address.
If you save a file, the macro recorder just records the choosen name of the file and does the same with any file, where the macro is running.
So you have to relativate the macro and make it dependend from the Sheet Name.
The name of the saved file hast to be constructed from your actual environment.
ie NameToSave = ActiveSheet.Name or similar.
As I can not say, what your macro recorder has recorded, it may be an option to post the file (with some empty, but named sheets and to post the file here, so we can correct the macro in the right way.