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 !
Bookmarks