• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 96
  • Last Modified:

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:

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

0
Dan Fuller
Asked:
Dan Fuller
  • 3
  • 3
1 Solution
 
NorieVBA ExpertCommented:
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

0
 
Dan FullerAuthor 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

0
 
NorieVBA ExpertCommented:
Dan

Where do you want to save the new workbook?

Also, what is the value in FO9 on the worksheet being copied?
0
Free Tool: Path Explorer

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.

 
Dan FullerAuthor 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)
0
 
NorieVBA ExpertCommented:
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

0
 
Dan FullerAuthor Commented:
That's perfect thank you for your help!
0
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

Featured Post

Free Tool: Port Scanner

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.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now