VBA to copy worksheet into new workbook and then save, close and return to original

Dan Fuller
Dan Fuller used Ask the Experts™
on
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: 

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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
NorieAnalyst Assistant

Commented:
Try this, I think it covers all the requirements you set out.
Sub SaveAsA0()
Dim wbA As Workbook
Dim wbB As Workbook
Dim strFileName As String

    Application.ScreenUpdating = False

    Set wbA = ThisWorkbook

    wbA.Sheets("Sheet1").Visible = True
    
    wbA.Sheets("Sheet1").Copy

    Set wbB = ActiveWorkbook

    With wbB
    
        With .Sheets(1).UsedRange
            .Copy
            .PasteSpecial xlValues
            .PasteSpecial xlFormats
        End With
        Application.CutCopyMode = False
        strFileName = .Sheets(1).Range("FO9").Value

        .SaveAs wbB.Path & Application.PathSeparator & strFileName & ".xlsm", xlOpenXMLWorkbookMacroEnabled
        .Close SaveChanges:=False
    End With

    wbA.Sheets("Sheet1").Visible = False
    
    Application.Goto wbA.Sheets("Sheet2").Range("AO64")
    
End Sub

Open in new window

Dan Fuller

Author

Commented:
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: 

.SaveAs wbB.Path & Application.PathSeparator & strFileName & ".xlsm", xlOpenXMLWorkbookMacroEnabled

Open in new window

NorieAnalyst Assistant

Commented:
Dan

Where do you want to save the new workbook?

Also, what is the value in FO9 on the worksheet being copied?
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Enroll Today!
Dan Fuller

Author

Commented:
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)
Analyst Assistant
Commented:
Dan

Oops, I made a bit of a typo - wbB,Path should be wbA.Path, try this.
      .SaveAs wbA.Path & Application.PathSeparator & strFileName & ".xlsm", xlOpenXMLWorkbookMacroEnabled

Open in new window

Dan Fuller

Author

Commented:
That's perfect thank you for your help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial