VBA Copy/Paste Special to New Worksheet

Posted on 2006-06-05
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
    LVL 80

    Expert Comment

    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


    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

    LVL 80

    Expert Comment

    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

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now