Using paste special when using Copy Destination

daren.beaney · Mar 4th 2011

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

Ped · Mar 4th 2011

Re: Using paste special when using Copy Destination

Code

Sub try()
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).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
x = x + 1
End Select
Next ws
End Sub

Display More

Ped · Mar 4th 2011

Re: Using paste special when using Copy Destination

I tried in one LINE but it error so try this way too

daren.beaney · Mar 4th 2011

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

daren.beaney · Mar 4th 2011

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!

AAE · Mar 5th 2011

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.

Code

Dim ws As Worksheet, nextrow As Long


On Error Resume Next


For Each ws In Worksheets
    Select Case UCase(ws.Name)
        Case "Index", "Data", "Summary", "Charts"
            'do nothing
        
        Case Else
            With Worksheets("Summary")
                nextrow = .Cells(Rows.Count, "B").End(xlUp).Row + 2
                .Range("B" & nextrow & ":D" & nextrow + 5) = ws.Range("Z6:AB11").Value
            End With
    End Select
Next ws

Display More

daren.beaney · Mar 5th 2011

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

Ped · Mar 6th 2011

Re: Using paste special when using Copy Destination

Great, thanks for sharing guys!

Using paste special when using Copy Destination

Participate now!

Share