Issues Copying and Pasting (using Copy method or using Cells().Value)

For Each sht In ThisWorkbook.Sheets
If sht.Name = PasteShtName Then
Set DestSht = sht
Exit For
End If
Next sht

I am not sure I understand why you are using this method to set your destination sheet to a variable. I think I would just use

Please Login or Register  to view this content.

Then exclude that sheet in the next For Each sht statement like

Please Login or Register  to view this content.

But like I said, I don't really understand why you are using that method. But if that method does not execute to set the variable, then you will get an error on the line saying the object varible is not set.

Hi !

There is no issue with respect of Excel object model and a serious read of VBA inner help !

DestSht.Range(Cells(copyrow, 1), Cells(copyrow, LastCol)).Value = …
This code may work only if the destination worksheet is active ! Why ? 'Cause there is no worksheet referencing Cells !

A good code whatever is the active worksheet is

DestSht.Range(DestSht.Cells(copyrow, 1), DestSht.Cells(copyrow, LastCol)).Value = …


As written in VBA inner help example when using Copy method, its Destination argument needs only the top left cell !

sht.Range(sht.Cells(RowsToCopy(rowNum), 1), sht.Cells(RowsToCopy(rowNum), LastCol)).Copy DestSht.Cells(copyrow, 1)


Whatever with Value or Copy, instead of Range use Cells & Resize :

sht.Cells(RowsToCopy(rowNum), 1).Resize(, LastCol).Copy DestSht.Cells(copyrow, 1)

TBTO rule : Think, But Think Object !