Macro Copy/Paste Excel Sheet

Question

I have the Macro code to copy and paste a list of values from Input, where Records are entered to Completed, the headers are the same.

Sub a()
Sheets("ITM").Range("A1:AD1000").Copy
Sheets("ISP").Range("A1").PasteSpecial xlValues
End Sub

This is working for the Copy and paste, however I need to adjust it to take into account existing filled fields, so that older Completed Records do not get over written.

Sample Data:

ITM:

User enters data

Header A   - Header B   -  Header C
  Ex          BY              TY

ISP

This has a record of all previous data that were entered, and macro will add the latest ISP entered data to next available row.

Request #        Header A    -      Header B    -  Header C
   1               XY                  TY              ER
   2               QW                  WE              EW
   3               EX                  BY              TY

Unknown Unknown · Accepted Answer · 2015-01-27T11:35:50

The Paste Special, values operation can be substituted with a direct transfer which does not involve the clipboard and is typically a little faster. By 'take into account existing filled fields', I will take it to mean that you want to put new information into the first blank row on the ISP worksheet and not overwrite previous records.

Sub a()
    With Sheets("ITM").Cells(1, 1).CurrentRegion
        With .Offset(1, 0).Resize(.Rows.Count - 1, 30)
            Sheets("ISP").Cells(Rows.Count, 1).End(xlUp) _
              .Offset(1, 1).Resize(.Rows.Count, .Columns.Count) = .Value
            Sheets("ISP").Cells(Rows.Count, 1).End(xlUp).Resize(.Rows.Count + 1, 1) _
              .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
        End With
    End With
End Sub

You didn't mention any column header labels so I did not account for them. If you have column header labels that you do not wish to bring across each time, the data range can be offset 1 row down.

Macro Copy/Paste Excel Sheet

1 Answers