Copy paste with macros

With ThisWorkbook

  'select the last row in a sheet
    r = .Worksheets(1).Range("A100").End(xlUp).Row

    'copy the rows from cell A1 to last diagnally filled cell
    .Worksheets(1).Range("A" & 1, "E" & r).Copy

  'activate the target worksheet and paste values and formats there        
    .Worksheets("Sheet2").Activate
     ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
     ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats
     
End With