VBA Run-time error 438 appears when "paste" runs

I'm just getting familiar with VBA and my code

For k = 3 To ThisWorkbook.Sheets.Count
ThisWorkbook.Sheets(k).Activate
ActiveSheet.Cells(11, 2).Select

lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Range("A5:" & "A" & CStr(lLastRow)).Copy
' ThisWorkbook.Sheets(1).Cells("B" & CStr(lFirstRow) & ":B" & CStr(lLastRow)).Select
ThisWorkbook.Sheets(1).Activate
ActiveSheet.Cells(lFirstRow, 2).Select
Selection.Paste
'Selection.PasteSpecial Paste:=xlPasteValues
'Selection.PasteSpecial Paste:=xlPasteFormats
lFirstRow = lFirstRow + lLastRow
Next k

makes "Run-time error 438. Object doesn't support this porperty or method" to appear when the line "Selection.Paste" goes. What's the problem?:( I've tried to use paste special, to activate sheet and to select cell (not range), to use Cstr, but nothing changed


Try Selection.PasteSpecial xlPasteAll

Paste by itself works on several objects, most notably Worksheet but not on a Range object which is what your Selection is.

To paste to a Range you really have to use the PasteSpecial method with its' available arguements such as xlPasteAll; xlPasteValues; xlPasteFormulas; xlPasteFormats and others which you can see by pressing F1 while the cursor is within PasteSpecial in the VBE.


Replace these two lines in your code

ActiveSheet.Cells(lFirstRow, 2).Select
Selection.Paste

by

Cells(lFirstRow, 2).Select
Activesheet.paste

your code will work flawlessly


Important note for working with paste and pastespecial in vba

Copy any range from anywhere then

  1. Paste Special method (Sheets.Cells/Range.PasteSpecial)

    Sheets ("Daily Shortage").Activate

    Sheets ("Daily Shortage").Cells (m, 1). PasteSpecial Paste: = xlPasteValues

One Example –

Will throw error

Sheets ("June"). Range ("A10").Select

ActiveSheet.PasteSpecial Paste: = xlPasteValues

This will work flawlessly

Sheets ("June"). Range ("A10").PasteSpecial Paste: = xlPasteValues
  1. Paste method (ActiveSheet.Paste)

    Sheets ("June"). Range ("A10").Select

    ActiveSheet.Paste