Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

Copy worksheets to new workbook

I need to create a macro/vba code to copy two worksheets to a new workbook. These two worksheets have calculations on it so I need copy and paste these worksheets as values in a new workbook. But I want to keep the formatting of the worksheets the same. And I also need to name this new workbook a certain name. Can anyone shed some light on how I might do this?
0
Lawrence Salvucci
Asked:
Lawrence Salvucci
  • 3
  • 2
1 Solution
 
Dave BrettVice President - Business EvaluationCommented:
This code copies two sheets (named Sheet1 and Sheet2) to a new workbook as values, preserving formatting

Cheers
Dave

Sub toNew()
    Dim ws As Worksheet
    Sheets(Array("Sheet1", "Sheet2")).Copy
    For Each ws In ActiveWorkbook.Worksheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
End Sub

Open in new window

0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you very much. One question...How can I have the name of the new workbook saved to something specific after I run this code to copy the worksheets?
0
 
Dave BrettVice President - Business EvaluationCommented:
No probs :)

Like so to save  c:\myfile.xlsx (with overwrite turned on by suppressing 'the file exists' message)

Cheers
Dave

Sub toNew()
    Dim ws As Worksheet
    Sheets(Array("Sheet1", "Sheet2")).Copy
    For Each ws In ActiveWorkbook.Worksheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "c:\myfile.xlsx"
     Application.DisplayAlerts = True
End Sub

Open in new window

0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Perfect! Thank you very much! I appreciate your quick response!
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thanks again! Much appreciated!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now