An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
VBA to copy worksheet into new workbook and then save, close and return to original
Hi,
I am looking at a macro that copies one worksheet (values and formats) from Workbook A into a new workbook (Workbook B). The sheet in question contains a cell (FO9) that I would like the new file name to be called.
I currently have the copy and paste function working ok, and all data has copied over correctly to the new workbook.
What I’m after is for the new workbook to “save as” automatically as an XLSM file using the name in cell FO9 in workbook B. The user must however be asked where to save it (save as). I then need this workbook to close and for the user to return back to cell AO64 in Workbook A, with Sheet 1 being hidden again, and for Sheet 2 to then become the active worksheet again.
The code I currently have is:
I am looking at a macro that copies one worksheet (values and formats) from Workbook A into a new workbook (Workbook B). The sheet in question contains a cell (FO9) that I would like the new file name to be called.
I currently have the copy and paste function working ok, and all data has copied over correctly to the new workbook.
What I’m after is for the new workbook to “save as” automatically as an XLSM file using the name in cell FO9 in workbook B. The user must however be asked where to save it (save as). I then need this workbook to close and for the user to return back to cell AO64 in Workbook A, with Sheet 1 being hidden again, and for Sheet 2 to then become the active worksheet again.
The code I currently have is:
Sub SaveAsA0()
Application.ScreenUpdating = False
Range("D3").Select
Sheets("Sheet 1").Visible = True
Sheets("Sheet 2").Visible = True
Sheets("Sheet 1").Select
ActiveSheet.Copy
With ActiveSheet.UsedRange
.Copy
.PasteSpecial xlValues
.PasteSpecial xlFormats
End With
Application.CutCopyMode = False
End Sub
Asked:
3-
3
1 Solution
Hi Nori,
Thanks for the info.
Unfortunately that gives me a debug error 1004 - saying Microsoft Excel cannot access the file C:\C3E9D080.
When I click on debug it highlights this bit of code:
Thanks for the info.
Unfortunately that gives me a debug error 1004 - saying Microsoft Excel cannot access the file C:\C3E9D080.
When I click on debug it highlights this bit of code:
.SaveAs wbB.Path & Application.PathSeparator & strFileName & ".xlsm", xlOpenXMLWorkbookMacroEnabled
Dan
Where do you want to save the new workbook?
Also, what is the value in FO9 on the worksheet being copied?
Where do you want to save the new workbook?
Also, what is the value in FO9 on the worksheet being copied?
Hi Norie,
The save location would ideally be chosen by the user (but worst case scenario to the same location as the original file).
The value in FO9 is a concatenate of other cells, made up from first name, surname and dob. It is as followsL
Surname, First Name mm-dd-yyyy (Email Version)
The save location would ideally be chosen by the user (but worst case scenario to the same location as the original file).
The value in FO9 is a concatenate of other cells, made up from first name, surname and dob. It is as followsL
Surname, First Name mm-dd-yyyy (Email Version)
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
Open in new window