Using paste special when using Copy Destination

  • Hi,

    I'm creating new worksheets using VBA and I need to populate a summary page. I'm using the following code which copies the content populated in the newly created worksheet to the summary sheet, however the content contains formulas and I need to only paste the values and the cell formatting.

    Code
    [FONT=Arial][size=10][COLOR=#17365d]For Each ws In Worksheets[/COLOR][/SIZE][/FONT]
    [FONT=Arial][size=10][COLOR=#17365d]Select Case UCase(ws.Name)[/COLOR][/SIZE][/FONT]
    [FONT=Arial][size=10][COLOR=#17365d]Case "Index", "Data", "Summary", "Charts"[/COLOR][/SIZE][/FONT]
    [FONT=Arial][size=10][COLOR=#17365d]'Do nothing[/COLOR][/SIZE][/FONT]
    [FONT=Arial][size=10][COLOR=#17365d]Case Else[/COLOR][/SIZE][/FONT]
    [FONT=Arial][size=10][COLOR=#17365d]ws.Range("Z6:AB11").Copy [/COLOR][/SIZE][/FONT][FONT=Arial][size=10][COLOR=#17365d]Destination:=Sheets("Summary").Range("B65536").End(xlUp).Offset(2, 0)[/COLOR][/SIZE][/FONT]
    [FONT=Arial][size=10][COLOR=#17365d]x = x + 1 [/COLOR][/SIZE][/FONT]
    [FONT=Arial][size=10][COLOR=#17365d]End Select[/COLOR][/SIZE][/FONT]
    [FONT=Arial][size=10][COLOR=#17365d]Next ws[/COLOR][/SIZE][/FONT]
    [FONT=Arial][size=10][COLOR=#17365d]

    [/COLOR][/SIZE][/FONT]

    All i see once the content has been pasted is #REF!.

    Thanks
    Daren

  • Re: Using paste special when using Copy Destination


  • Re: Using paste special when using Copy Destination


    Hi Ped,

    Unfortunately I got a "Run-time error 1004: Selec method of Range class failed"

    It's related to this line

    Code
    Sheets("Summary").Range("B" & Rows.Count).End(xlUp).Offset(2, 0).Select
  • Re: Using paste special when using Copy Destination


    Ped,

    I sorted it, see below:

    Code
    For Each ws In Worksheets
    Select Case UCase(ws.Name)
    Case "Index", "Data", "Summary", "Charts"
    Case Else
    ws.Range("Z6:AB11").Copy
    Sheets("Summary").Range("B" & Rows.Count).End(xlUp).Offset(2, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    x = x + 1
    End Select
    Next ws



    Thanks for your help!

  • Re: Using paste special when using Copy Destination


    Your variable of "x' does nothing in the code you posted. Is it being using elsewhere in the code, as a counter?


    You can also achieve this without copying, adjust ranges to adapt to your sheets.


  • Re: Using paste special when using Copy Destination


    Hi,

    I adapted the code which included x = 0 so I left it there as it worked, will try your approach as well thanks.

    Daren

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!