Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 791
Default Macro to Copy data from a list in sheet1 and paste into sheet2

I have three columns in sheet1 that need to copied into sheet2 when a new row
is created in sheet1.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,388
Default Macro to Copy data from a list in sheet1 and paste into sheet2

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.

"Michael" wrote:

I have three columns in sheet1 that need to copied into sheet2 when a new row
is created in sheet1.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 791
Default Macro to Copy data from a list in sheet1 and paste into sheet2

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:


"Dave" wrote:

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.

"Michael" wrote:

I have three columns in sheet1 that need to copied into sheet2 when a new row
is created in sheet1.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,388
Default Macro to Copy data from a list in sheet1 and paste into sheet2

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
DataVaildationList works in Sheet1, but does not in Sheet2; Why rseeley Excel Discussion (Misc queries) 1 December 11th 07 11:19 PM
how do copy "sheet1!A1+1 in sheet2 to sheet 3 and get "sheet2!A1+ Dany Excel Discussion (Misc queries) 5 April 16th 07 03:27 AM
List a group of repeated data from sheet1 to sheet2 but not repeat Tasmania New Users to Excel 1 January 26th 07 10:08 PM
Copy result from sheet1 to sheet2 Winnie Excel Discussion (Misc queries) 3 June 26th 06 09:22 AM
Copy values from Sheet1 to Sheet2 Eintsein_mc2 Excel Discussion (Misc queries) 1 January 6th 05 06:02 AM


All times are GMT +1. The time now is 02:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"