Forum Discussion

Malcolm McMaster's avatar
Malcolm McMaster
Copper Contributor
Jun 02, 2017

Macro to copy data to another sheet

Hi All,

 

I have a worksheet that serves as a data entry form & another worksheet that stores historic data.

I would like to be able to enter the new data in the data entry form & then by macro, paste the new data into the history storeage worksheet. Obviously the pasting process needs to be directed to the next vacant row of cells.

I'm using Excel within office 365. Any help would be greatly appreciated.

 

Excel Desktop
Macros & VBA
  • Smitty Smith's avatar
    Smitty Smith
    Icon for Microsoft rankMicrosoft
    Jun 05, 2017

    See if this gets you started:

     

    Sub foo()
    Dim lr As Long
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    
    Set wsSource = Sheets("Input")
    Set wsDest = Sheets("Data")
    
    lr = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    wsDest.Range("A" & lr).Value = wsSource.Range("A1").Value
    wsDest.Range("B" & lr).Value = wsSource.Range("B1").Value
    wsDest.Range("C" & lr).Value = wsSource.Range("C1").Value
    wsDest.Range("D" & lr).Value = wsSource.Range("D1").Value
    
End Sub

     

    It copies A1:D1 from one sheet to the next empty row in another sheet. You'll need to adjust your input ranges to suit. The lr method is what sets the next empty row.

     

    HTH 

Resources