VBA Copy/Paste Special to New Worksheet

Can someone please assist me with a VBA routine to duplicate a worksheet to a new worksheet, using paste special?

Thanks much,
Who is Participating?
swilschutzAuthor Commented:
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,

swilschutzAuthor Commented:

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?

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
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.

All Courses

From novice to tech pro — start learning today.