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
-
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
-
Paste method (
ActiveSheet.Paste)Sheets ("June"). Range ("A10").Select
ActiveSheet.Paste