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


VBA Copy/Paste Special to New Worksheet

Posted on 2006-06-05
Medium Priority
Last Modified: 2012-05-05
Can someone please assist me with a VBA routine to duplicate a worksheet to a new worksheet, using paste special?

Thanks much,
Question by:swilschutz
  • 2
  • 2
LVL 81

Expert Comment

ID: 16838791
Hi swilschutz,
You didn't specify whether the copy should be to a new workbook or to the same one. Here are both alternatives:

Sub CopySheet()
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Copy     'Create a new workbook from the worksheet
'Worksheets.Add after:=Worksheets(Worksheets.Count)  'Create a new worksheet in the same workbook
ws.Cells.Copy ActiveSheet.Cells      'This statement needed in case a cell contains more than 255 characters. It also copies the sheet formatting.
End Sub

Hoping to be helpful,


Author Comment

ID: 16842134

I'm sorry; I reread my question and I wasn't very clear.

I need the new worksheet in the same workbook, but I would like to paste special (values), and thus strip the formatting.

The whole process is this:
1. Copy data from web site into Excel sheet.  When copied, it comes with merged cells, and strange formatting.
2. Copy and paste special (values) into new worksheet.
3. Clean data and remove unnecessary data -- already programmed a VBA routine to do this.

Can I modify the VBA routine above to do a Paste Special with xlValues?


Accepted Solution

swilschutz earned 0 total points
ID: 16842525
LVL 81

Expert Comment

ID: 16842667
Since you found your own answer, the right way to handle the situation is to refund your points (500) and give you credit for the answer. I've taken care of this for you.
byundt--Excel TA Page Editor

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

577 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