Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Copy worksheet to a new workbook

Posted on 2014-03-09
5
Medium Priority
?
374 Views
Last Modified: 2014-03-09
I'm using the following code to copy a sheet to a new workbook but it's only copying the values, not the formulas. How can I modify this code to copy the formulas too?

Sub CopyToNew()
Dim ws As Worksheet
    Sheets(Array("SO")).Copy
    For Each ws In ActiveWorkbook.Worksheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "\\BCAR1\BCAR-Net\IT\Excel Spreadsheets\Daily SO Report\Daily SO Report.xlsx"
    Application.DisplayAlerts = True
End Sub

Open in new window

0
Comment
Question by:Lawrence Salvucci
  • 2
  • 2
5 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 39916004
this code is not copying the value !!!

What do you want to copy ?

It is copying
Sheets(Array("SO")).Copy

but it is not pasting it.

what do you have sheets that you need to copy ?
gowflow
0
 
LVL 31

Accepted Solution

by:
gowflow earned 1000 total points
ID: 39916026
ok in your code replace

this line
ws.UsedRange.Value = ws.UsedRange.Value


by this line
ws.Paste


so that the code becomes

Sub CopyToNew()
Dim ws As Worksheet
    Sheets("sheet1").Copy
    For Each ws In ActiveWorkbook.Worksheets
        ws.Paste
    Next ws
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "\\BCAR1\BCAR-Net\IT\Excel Spreadsheets\Daily SO Report\Daily SO Report.xlsx"
    Application.DisplayAlerts = True
End Sub

Open in new window

0
 
LVL 35

Assisted Solution

by:Norie
Norie earned 1000 total points
ID: 39916042
Remove this, it's the part of the code that's replacing formulas with values.
For Each ws In ActiveWorkbook.Worksheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws

Open in new window

0
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
ID: 39916107
Thank you very much for your quick responses!
0
 
LVL 35

Expert Comment

by:Norie
ID: 39916131
goflow

What's being pasted here?
ws.Paste

Open in new window

When I try the code it's the last thing that was copied to the clipboard, and nothing to do with the worksheet that's been copied.
0

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question