.PasteSpecial Issues Using External Workbooks

[tjiddy89]

I'm trying to generate a script that locates the last row with a value contained within it and store it in a variable. I then use said variable in a range and then copy the data from an external (closed) workbook. I am able to see that it copies the data correctly, however when it gets down to the pastespecial function I run into a 1004 runtime "Application-defined or object-defined error".

Thank you in advance for your assistance.

lRow = Workbooks(qc).Sheets(2).Cells.Find(What:="*", _  After:=Range("A1"), _
  LookAt:=xlPart, _
  LookIn:=xlFormulas, _
  SearchOrder:=xlByRows, _
  SearchDirection:=xlPrevious, _
  MatchCase:=False).row

'Tried the below method as well to no success.

'Workbooks(dest).Sheets(2).Range(Workbooks(dest).Sheets(2).Cells(2, 2), Workbooks(dest).Sheets(2).Cells(lRow, 34)).Value = Workbooks(qc).Sheets(2).Range(Workbooks(qc).Sheets(2).Cells(1, 1), Workbooks(qc).Sheets(2).Cells(lRow, 33)).SpecialCells(xlCellTypeVisible)
    
'This is because the destination sheet's cells will be in a different area.
lRow = lRow + 2

'Below is where I'm getting the 1004 error.  More specifically the Workbook(dest) line.
Workbooks(qc).Sheets(2).Cells.SpecialCells(xlCellTypeVisible).Copy
Workbooks(dest).Worksheets(2).Range("B3:" & Workbooks(dest).Worksheets(2).Cells(lRow, 33)).PasteSpecial xlPasteValues

 

[Fluff]

Try

Workbooks(dest).Worksheets(2).Range("B3").PasteSpecial xlPasteValues

 

[tjiddy89]

I got another 1004 PasteSpecial method of range class failed

 

[Fluff]

Do you have any merged cells, or sheet protection?

 

[Fluff]

Change of plan, try

Workbooks(dest).Worksheets(2).Range("[COLOR=#ff0000]A[/COLOR]3").PasteSpecial xlPasteValues

 

[tjiddy89]

That worked perfectly! Thank you Fluff, much appreciated!

 

[tjiddy89]

Change of plan, try

Workbooks(dest).Worksheets(2).Range("[COLOR=#ff0000]A[/COLOR]3").PasteSpecial xlPasteValues

Do you know why I can't paste into cell B3?

 

[Fluff]

You're copying the entire row, therefore you have to paste into col A.