Copy & Paste special

[cgriff]

I have code below and I am trying to paste special values and formatting with no luck, any help would be greatly appreciated

Chris


Sub Copy_Paste_Below_Last_Cell_new()




'Find the last used row in both sheets and copy and paste data below existing data.

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long



'Set variables for copy and destination sheets
Set wsCopy = ThisWorkbook.Worksheets(8)
Set wsDest = ThisWorkbook.Worksheets("Data collect")

'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

'3. Copy & Paste Data
wsCopy.Range("A2:G" & lCopyLastRow).Copy _
wsDest.Range("A" & lDestLastRow)


End Sub

 

[MARK858]

Try changing

wsCopy.Range("A2:G" & lCopyLastRow).Copy _
wsDest.Range("A" & lDestLastRow)

to

    wsCopy.Range("A2:G" & lCopyLastRow).Copy
    
    With wsDest.Range("A" & lDestLastRow)
        .PasteSpecial xlValues
        .PasteSpecial xlFormats
    End With

 

[MARK858]

You should also add

Application.CutCopyMode = False

after the code I posted

 

[cgriff]

Thank you very much al working perfect

 

[Scole1]

I cant get this to work for the life of me. Ive been staring at this and cant figure out where its wrong. I just want to paste values. i added paste special and get error

Sub Paste_Matrix()
'Find the last used row in both sheets and copy and paste data below existing data.

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

'Set variables for copy and destination sheets
Set wsCopy = Workbooks("test.xlsm").Worksheets("Matrix")
Set wsDest = Workbooks("Test2.xlsx").Worksheets("Matrix")

'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

'3. Copy & Paste Data
wsCopy.Range("A1:J" & lCopyLastRow).Copy_
wsDest.Range ("A" & lDestLastRow)
.PasteSpecial xlValues
.PasteSpecial xlFormats


Application.CutCopyMode = False

End Sub

 

[Fluff]

See post#2

 

[Scole1]

See post#2

When I run the original VBA image#1 it work but paste formulas, but when I add the special i get image#2 it give compile error
Image#1

Image#2

 

[MARK858]

You still haven't looked at post number 2 properly