|
excel macro to copy data to second sheet
Hi,
I have a worksheet containing details of weekly takings, i would like to have a macro that will copy certain cell data and add it to the next available row of sheet 2, any ideas please? |
excel macro to copy data to second sheet
Hi Stuart
See http://www.rondebruin.nl/copy1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... Hi, I have a worksheet containing details of weekly takings, i would like to have a macro that will copy certain cell data and add it to the next available row of sheet 2, any ideas please? |
excel macro to copy data to second sheet
Thanks for the response Ron,
How will it work if i just want say 3 specific cells copied to the second sheet? "Ron de Bruin" wrote: Hi Stuart See http://www.rondebruin.nl/copy1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... Hi, I have a worksheet containing details of weekly takings, i would like to have a macro that will copy certain cell data and add it to the next available row of sheet 2, any ideas please? |
excel macro to copy data to second sheet
Hi Stuart
Which cells ? Paste Below each other or next to each other on one row in the database sheet ? -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... Thanks for the response Ron, How will it work if i just want say 3 specific cells copied to the second sheet? "Ron de Bruin" wrote: Hi Stuart See http://www.rondebruin.nl/copy1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... Hi, I have a worksheet containing details of weekly takings, i would like to have a macro that will copy certain cell data and add it to the next available row of sheet 2, any ideas please? |
excel macro to copy data to second sheet
I would like to copy a cell that contains the week ending date, the total tax
paid and total cash income and record this on one row of the database sheet, i can send you the sheet if it would help "Ron de Bruin" wrote: Hi Stuart Which cells ? Paste Below each other or next to each other on one row in the database sheet ? -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... Thanks for the response Ron, How will it work if i just want say 3 specific cells copied to the second sheet? "Ron de Bruin" wrote: Hi Stuart See http://www.rondebruin.nl/copy1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... Hi, I have a worksheet containing details of weekly takings, i would like to have a macro that will copy certain cell data and add it to the next available row of sheet 2, any ideas please? |
excel macro to copy data to second sheet
Hi Stuart
I always do this if i want this Below my data in ror 100 for example I do this in A100:C100 (you can hide that row) I use a formula like this =A1, =C3, =E5 I use this example then http://www.rondebruin.nl/copy1.htm#range1 But this is a other way to do it for you for cell A1,C3, E5 in "Sheet1" Sub copy_2() Dim destrange As Range Dim lr As Long Dim cell As Range Dim Cnum As Long Cnum = 0 lr = LastRow(Sheets("Sheet2")) + 1 For Each cell In Sheets("Sheet1"). _ Range("A1,C3,E5") Cnum = Cnum + 1 Set destrange = Sheets("Sheet2").Cells(lr, Cnum) cell.Copy destrange Next cell End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... I would like to copy a cell that contains the week ending date, the total tax paid and total cash income and record this on one row of the database sheet, i can send you the sheet if it would help "Ron de Bruin" wrote: Hi Stuart Which cells ? Paste Below each other or next to each other on one row in the database sheet ? -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... Thanks for the response Ron, How will it work if i just want say 3 specific cells copied to the second sheet? "Ron de Bruin" wrote: Hi Stuart See http://www.rondebruin.nl/copy1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... Hi, I have a worksheet containing details of weekly takings, i would like to have a macro that will copy certain cell data and add it to the next available row of sheet 2, any ideas please? |
excel macro to copy data to second sheet
Dim destrange As Range
Dim lr As Long Dim cell As Range Dim Cnum As Long Cnum = 0 lr = LastRow(Sheets("Sheet2")) + 1 For Each cell In Sheets("Sheet1"). _ Range("D3,K47,K53") Cnum = Cnum + 1 Set destrange = Sheets("Sheet2").Cells(lr, Cnum) cell.Copy destrange Next cell If i execute this i get a subscript out of range error and this line is highlighted lr = LastRow(Sheets("Sheet2")) + 1 I really appreciate your help..... "Ron de Bruin" wrote: Hi Stuart I always do this if i want this Below my data in ror 100 for example I do this in A100:C100 (you can hide that row) I use a formula like this =A1, =C3, =E5 I use this example then http://www.rondebruin.nl/copy1.htm#range1 But this is a other way to do it for you for cell A1,C3, E5 in "Sheet1" Sub copy_2() Dim destrange As Range Dim lr As Long Dim cell As Range Dim Cnum As Long Cnum = 0 lr = LastRow(Sheets("Sheet2")) + 1 For Each cell In Sheets("Sheet1"). _ Range("A1,C3,E5") Cnum = Cnum + 1 Set destrange = Sheets("Sheet2").Cells(lr, Cnum) cell.Copy destrange Next cell End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... I would like to copy a cell that contains the week ending date, the total tax paid and total cash income and record this on one row of the database sheet, i can send you the sheet if it would help "Ron de Bruin" wrote: Hi Stuart Which cells ? Paste Below each other or next to each other on one row in the database sheet ? -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... Thanks for the response Ron, How will it work if i just want say 3 specific cells copied to the second sheet? "Ron de Bruin" wrote: Hi Stuart See http://www.rondebruin.nl/copy1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... Hi, I have a worksheet containing details of weekly takings, i would like to have a macro that will copy certain cell data and add it to the next available row of sheet 2, any ideas please? |
excel macro to copy data to second sheet
Hi Stuart
You need the function also I posted it also and you can read about it on my website -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... Dim destrange As Range Dim lr As Long Dim cell As Range Dim Cnum As Long Cnum = 0 lr = LastRow(Sheets("Sheet2")) + 1 For Each cell In Sheets("Sheet1"). _ Range("D3,K47,K53") Cnum = Cnum + 1 Set destrange = Sheets("Sheet2").Cells(lr, Cnum) cell.Copy destrange Next cell If i execute this i get a subscript out of range error and this line is highlighted lr = LastRow(Sheets("Sheet2")) + 1 I really appreciate your help..... "Ron de Bruin" wrote: Hi Stuart I always do this if i want this Below my data in ror 100 for example I do this in A100:C100 (you can hide that row) I use a formula like this =A1, =C3, =E5 I use this example then http://www.rondebruin.nl/copy1.htm#range1 But this is a other way to do it for you for cell A1,C3, E5 in "Sheet1" Sub copy_2() Dim destrange As Range Dim lr As Long Dim cell As Range Dim Cnum As Long Cnum = 0 lr = LastRow(Sheets("Sheet2")) + 1 For Each cell In Sheets("Sheet1"). _ Range("A1,C3,E5") Cnum = Cnum + 1 Set destrange = Sheets("Sheet2").Cells(lr, Cnum) cell.Copy destrange Next cell End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... I would like to copy a cell that contains the week ending date, the total tax paid and total cash income and record this on one row of the database sheet, i can send you the sheet if it would help "Ron de Bruin" wrote: Hi Stuart Which cells ? Paste Below each other or next to each other on one row in the database sheet ? -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... Thanks for the response Ron, How will it work if i just want say 3 specific cells copied to the second sheet? "Ron de Bruin" wrote: Hi Stuart See http://www.rondebruin.nl/copy1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... Hi, I have a worksheet containing details of weekly takings, i would like to have a macro that will copy certain cell data and add it to the next available row of sheet 2, any ideas please? |
excel macro to copy data to second sheet
its ok i sorted it, replaced sheet1, sheet2 with my sheet names.
Thanks again for your help "Ron de Bruin" wrote: Hi Stuart You need the function also I posted it also and you can read about it on my website -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... Dim destrange As Range Dim lr As Long Dim cell As Range Dim Cnum As Long Cnum = 0 lr = LastRow(Sheets("Sheet2")) + 1 For Each cell In Sheets("Sheet1"). _ Range("D3,K47,K53") Cnum = Cnum + 1 Set destrange = Sheets("Sheet2").Cells(lr, Cnum) cell.Copy destrange Next cell If i execute this i get a subscript out of range error and this line is highlighted lr = LastRow(Sheets("Sheet2")) + 1 I really appreciate your help..... "Ron de Bruin" wrote: Hi Stuart I always do this if i want this Below my data in ror 100 for example I do this in A100:C100 (you can hide that row) I use a formula like this =A1, =C3, =E5 I use this example then http://www.rondebruin.nl/copy1.htm#range1 But this is a other way to do it for you for cell A1,C3, E5 in "Sheet1" Sub copy_2() Dim destrange As Range Dim lr As Long Dim cell As Range Dim Cnum As Long Cnum = 0 lr = LastRow(Sheets("Sheet2")) + 1 For Each cell In Sheets("Sheet1"). _ Range("A1,C3,E5") Cnum = Cnum + 1 Set destrange = Sheets("Sheet2").Cells(lr, Cnum) cell.Copy destrange Next cell End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... I would like to copy a cell that contains the week ending date, the total tax paid and total cash income and record this on one row of the database sheet, i can send you the sheet if it would help "Ron de Bruin" wrote: Hi Stuart Which cells ? Paste Below each other or next to each other on one row in the database sheet ? -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... Thanks for the response Ron, How will it work if i just want say 3 specific cells copied to the second sheet? "Ron de Bruin" wrote: Hi Stuart See http://www.rondebruin.nl/copy1.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Stuart" wrote in message ... Hi, I have a worksheet containing details of weekly takings, i would like to have a macro that will copy certain cell data and add it to the next available row of sheet 2, any ideas please? |
| All times are GMT +1. The time now is 03:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com