Macro to Copy data from a list in sheet1 and paste into sheet2

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

I have three columns in sheet1 that need to copied into sheet2 when a new row
is created in sheet1.
 
Hi Michael,
Do you need to copy and paste all the data each time, or just the newly
created row?
If you want all the data each time, do you want to overwrite the copy/paste
from last time, or paste it into a new place?
How big is your data range in Sheet1? Is it in Columns A, B and C?
Do you want the copy/paste procedure to be automatic, or do you want to
activate it with a button or keyboard shortcut?
Do you like vegemite?

Regards - Dave.
 
It should only copy the newly created row only.
So when I add a new row and input the data into columns A, B, and C I would
like the new row data to be copied to other sheets. Automatic copy/paste or a
button would work perfectly.

I have never had vegemite:>
 
Hi Michael,

This code should do what you want.
One of the pro's could probably do it in fewer lines, but it works ok.

The code finds the last populated row on sheet1, the first unpopulated row
on Sheet2, then does the copy and paste.
I think a button would work best.
Note that the code does not detect if a new Row of data has been added to
Sheet1. If you push the button multiple times, the last row on sheet1 will be
copied multiple times to successive rows on sheet2.
New-data detection could be built in if necessary - I think
If your sheet names aren't Sheet1 and Sheet2, you'll have to change the code
accordingly.
As it is now, when the macro finishes, Sheet2 is the active sheet.
If you want Sheet1 to be active after running, remove the apostrophe from
the beginning of the last line.

Never tried Vegemite? Oh dear oh dear. Is this possible?

Sub CopyLastRow()
Dim A As Integer, B As Integer
A = 1
B = 1
Do Until Worksheets("Sheet1").Cells(A + 1, 1) = ""
A = A + 1
Loop
Do Until Worksheets("Sheet2").Cells(B, 1) = ""
B = B + 1
Loop
Sheets("Sheet1").Select
Worksheets("Sheet1").Range(Cells(A, 1), Cells(A, 3)).Copy
Sheets("Sheet2").Select
Range(Cells(B, 1), Cells(B, 3)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Sheets("Sheet1").Select
End Sub
 
Back
Top