Solved

Copy worksheet to a new workbook

Posted on 2014-03-09
5
368 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 30

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 30

Accepted Solution

by:
gowflow earned 250 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 34

Assisted Solution

by:Norie
Norie earned 250 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 34

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

739 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