VBA PasteSpecial causing Run-time error

[emojo689]

Good Morning,

I have a workbook containing 7 sheets (4 of which are 'project list' working sheets, and 1 a summary sheet 'Gantt'). The gantt sheet has 4 contiguous tables with headers and a gantt chart line for each listed project in column D. It's format is not the same as the working sheets and I need to keep it that way.

What I'm basically doing with the following code is copying certain columns only from each project worksheet and pasting into the summary Gantt sheet under the relevant tables. Everything works perfectly for me except for the PasteSpecial xlPasteValues portion inserted in thedestination piece. If it is included, Ireceive a run-time error. When I don’thave it included, it copies everything over, but of course it copies it’sformatting as well. I just want thevalues copied over so I can retain my formatting in the Gantt sheet.

Like I said, I really need to keep my Gantt sheet formatting– any ideas?


Sub UpdateSummary()

'clinical table for COPY job

Dim clintbl As ListObject, clinsize As Long, lastC As Long
Set clintbl =Worksheets("ClinicalMicro").ListObjects("TClinical")
clinsize =clintbl.ListRows.Count
lastC =clintbl.HeaderRowRange.Offset(clinsize + 1).End(xlUp).Row

'clinical table for PASTE job

Dim tbl As ListObject, tblGC As Long, tblsize As Integer, clinarray AsRange

Set tbl =Worksheets("Gantt").ListObjects("Clinical")
tblGC = tbl.ListRows.Count
tblsize = clinsize - tblGC

If tblGC < clinsize Then
tbl.Range.EntireRow.Offset(2).Resize(tblsize).Insert Shift:=xlDown

End If

WithWorksheets("ClinicalMicro")

Application.Union(.Range("B2:B" & lastC),.Range("D2:D" & lastC), .Range("F2:F" & lastC),.Range("H2:H" & lastC)).Copy _
Destination:=tbl.HeaderRowRange.Offset(1)

EndWith

 

[Fluff]

It would help if you showed us the code that doesn't work

 

[AlphaFrog]

Did you use this syntax?

Application.Union(.Range("B2:B" & lastC), .Range("D2:D" & lastC), .Range("F2:F" & lastC), .Range("H2:H" & lastC)).Copy
tbl.HeaderRowRange.Offset(1).PasteSpecial xlPasteValues
Application.CutCopyMode = False

 

[emojo689]

Thanks for the quick reply, and Sorry.
Yes, that is the syntax I used, except more specifically as:
" Application.Union(.Range("B2:B" & lastC),.Range("D2:D" & lastC), .Range("F2:F" & lastC),.Range("H2:H" & lastC)).Copy _
Destination:=tbl.HeaderRowRange.Offset(1) .PasteSpecial xlPasteValues "

btw: I'm using Windows 7, Office 2010 (unfortunately at the moment)

 

[AlphaFrog]

Thanks for the quick reply, and Sorry.
Yes, that is the syntax I used, except more specifically as:
" Application.Union(.Range("B2:B" & lastC),.Range("D2:D" & lastC), .Range("F2:F" & lastC),.Range("H2:H" & lastC)).Copy _
Destination:=tbl.HeaderRowRange.Offset(1) .PasteSpecial xlPasteValues "

btw: I'm using Windows 7, Office 2010 (unfortunately at the moment)

That is not the same syntax as I suggested. The red stuff is incorrect for PasteSpecial. Copy all of my code and try it.

 

[emojo689]

Funny....I had what you suggested before I added the "destination" part and it kept throwing an "expected end of statement" so I went with the destination part. Putting the actual destination piece on a separate line worked. Go figure.

Works perfectly!!! Thanks a lot for your help