better way to copy and paste vba

[daveyc18]

finalrow = Cells(Rows.Count, "A").End(xlUp).Row



'date

Range("a2:a" & finalrow).SpecialCells(xlCellTypeVisible).Copy



''
rpt100.Activate

totalrow = Cells(Rows.Count, "A").End(xlUp).Row + 1

Range("o" & totalrow).PasteSpecial xlPasteValues

''

LML.Activate

'account
Range("b2:b" & finalrow).SpecialCells(xlCellTypeVisible).Copy



''
rpt100.Activate


Range("c" & totalrow).PasteSpecial xlPasteValues




''

LML.Activate

'inv
Range("n2:n" & finalrow).SpecialCells(xlCellTypeVisible).Copy



''
rpt100.Activate


Range("a" & totalrow).PasteSpecial xlPasteValues


''

constarntly going back and forth

 

[saboh12617]

Hi,
To remove the back and forth, you can do two things:
1) Add Application.ScreenUpdating = False at the start of the macro
(and Application.ScreenUpdating = True at the end)
2) Instead of activating the sheets, refer to the ranges with the sheet identifier. Like so (if i understand correctly the code) :
Instead of

LML.Activate
'account
Range("b2:b" & finalrow).SpecialCells(xlCellTypeVisible).Copy

Do

'account
LML.Range("b2:b" & finalrow).SpecialCells(xlCellTypeVisible).Copy

 

[daveyc18]

Hi,
To remove the back and forth, you can do two things:
1) Add Application.ScreenUpdating = False at the start of the macro
(and Application.ScreenUpdating = True at the end)
2) Instead of activating the sheets, refer to the ranges with the sheet identifier. Like so (if i understand correctly the code) :
Instead of

LML.Activate
'account
Range("b2:b" & finalrow).SpecialCells(xlCellTypeVisible).Copy

Do

'account
LML.Range("b2:b" & finalrow).SpecialCells(xlCellTypeVisible).Copy

what about pasting? would this work?

RP100.Range("a" & cells(rows.count,"A").end(xlup).row+1).PasteSpecial xlPasteValues

 

[MARK858]

RP100.Range("A" & RP100.Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial xlPasteValues

If you are just pasting to the cell one down from the last cell with data in column A of RP100