VB code to paste values

[pagrender]

Hello all,

I'm using the following code to paste data from all sheets onto one sheet. Is there a way to alter this code so it pastes as values? This code is bringing in some formulas and I would like to have values instead.


Dim i As Integer, lr As Long, lr2 As Long

For i = 1 To Sheets.Count
If IsError(Application.Match(Sheets(i).Name, Array("AllData", "Totals", "LBA"), 0)) Then
lr = Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("AllData").Cells(Rows.Count, "A").End(xlUp).Offset(1).Row
Sheets(i).Range("A1:H" & lr).Copy Destination:=Sheets("AllData").Range("A" & lr2)
End If
Next i

 

[Smitty]

How's this:

Sheets(i).Range("A1:H" & lr).Copy
        Sheets("AllData").Range("A" & lr2).PasteSpecial Paste:=xlPasteValues

HTH,

 

[VoG]

Try

Dim i As Integer, lr As Long, lr2 As Long

For i = 1 To Sheets.Count
    If IsError(Application.Match(Sheets(i).Name, Array("AllData", "Totals", "LBA"), 0)) Then
        lr = Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row
        lr2 = Sheets("AllData").Cells(Rows.Count, "A").End(xlUp).Offset(1).Row
        Sheets(i).Range("A1:H" & lr).Copy
        Sheets("AllData").Range("A" & lr2).PasteSpecial Paste:=xlPasteValues
    End If
Next i

 

[pagrender]

Looks like that did the trick. Thanks!