Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Moving cells to another workbook sequentially
Hi Hope someone can help. I need a macro to enable moving two individual cells from one workbook to another workbook. In the first sheet *(Daily)* the cells will always be entered on the same row. A summary will be kept each time the row has different data written to it (every day) on a second sheet *(Summary), * so they need to be positioned there sequentially. eg cells b4 and d4 in workbook Daily need a macro to be moved to the Summary workbook - first time to cells f2 and g2 - then next time to row beneath and so on each time macro is run. Hope this makes sense! Thanks Jenno -- Jenno ------------------------------------------------------------------------ Jenno's Profile: http://www.excelforum.com/member.php...o&userid=26236 View this thread: http://www.excelforum.com/showthread...hreadid=397691 |
#2
|
|||
|
|||
Both workbooks need to be open for this to work. And it finds the next
available row by starting at the bottom of column F and going to the top (then down one): Option Explicit Sub testme() Dim fWks As Worksheet Dim tWks As Worksheet Dim DestCell As Range Set fWks = Workbooks("book1.xls").Worksheets("sheet1") Set tWks = Workbooks("book2.xls").Worksheets("sheet1") With fWks If IsEmpty(.Range("b4")) _ Or IsEmpty(.Range("d4")) Then MsgBox "please put something in both B4 and D4" Exit Sub End If With tWks Set DestCell = .Cells(.Rows.Count, "F").End(xlUp).Offset(1, 0) End With DestCell.Value = .Range("b4").Value DestCell.Offset(0, 1).Value = .Range("d4").Value End With End Sub Jenno wrote: Hi Hope someone can help. I need a macro to enable moving two individual cells from one workbook to another workbook. In the first sheet *(Daily)* the cells will always be entered on the same row. A summary will be kept each time the row has different data written to it (every day) on a second sheet *(Summary), * so they need to be positioned there sequentially. eg cells b4 and d4 in workbook Daily need a macro to be moved to the Summary workbook - first time to cells f2 and g2 - then next time to row beneath and so on each time macro is run. Hope this makes sense! Thanks Jenno -- Jenno ------------------------------------------------------------------------ Jenno's Profile: http://www.excelforum.com/member.php...o&userid=26236 View this thread: http://www.excelforum.com/showthread...hreadid=397691 -- Dave Peterson |
#3
|
|||
|
|||
Thanks for your help Dave, the only trouble is when I run this I get a run time *error 9 subscript out of range*. Is that something simple I can fix? Sorry for the delay with this but today is the first opportunity I have had to test this. Jenno -- Jenno ------------------------------------------------------------------------ Jenno's Profile: http://www.excelforum.com/member.php...o&userid=26236 View this thread: http://www.excelforum.com/showthread...hreadid=397691 |
#4
|
|||
|
|||
My guess is that you're gonna get the error on both these lines:
Set fWks = Workbooks("book1.xls").Worksheets("sheet1") Set tWks = Workbooks("book2.xls").Worksheets("sheet1") You'll have to make sure you use the correct workbook names and the correct worksheet names. (I used book1.xls and book2.xls and sheet1.) If that's not the problem, post back with the code you used and the lines that caused the error. Jenno wrote: Thanks for your help Dave, the only trouble is when I run this I get a run time *error 9 subscript out of range*. Is that something simple I can fix? Sorry for the delay with this but today is the first opportunity I have had to test this. Jenno -- Jenno ------------------------------------------------------------------------ Jenno's Profile: http://www.excelforum.com/member.php...o&userid=26236 View this thread: http://www.excelforum.com/showthread...hreadid=397691 -- Dave Peterson |
#5
|
|||
|
|||
Hi Dave I used book1 and book2 (as in new excel workbooks) to test this macro, and I just copied and pasted the coding you kindly provided, deleting the Option Explicit line and extra end sub as generated by the program. Jenno -- Jenno ------------------------------------------------------------------------ Jenno's Profile: http://www.excelforum.com/member.php...o&userid=26236 View this thread: http://www.excelforum.com/showthread...hreadid=397691 |
#6
|
|||
|
|||
Did you use book1.xls and book2.xls?
If you did, then you had to save the workbooks before running the code. If you didn't save, then just use book1 and book2. And each of those workbooks did have a sheet named sheet1?? I don't know what the "extra end sub" line was. If you still have trouble, post the code and the names of the workbooks. Jenno wrote: Hi Dave I used book1 and book2 (as in new excel workbooks) to test this macro, and I just copied and pasted the coding you kindly provided, deleting the Option Explicit line and extra end sub as generated by the program. Jenno -- Jenno ------------------------------------------------------------------------ Jenno's Profile: http://www.excelforum.com/member.php...o&userid=26236 View this thread: http://www.excelforum.com/showthread...hreadid=397691 -- Dave Peterson |
#7
|
|||
|
|||
Hi Dave Just tried out what you suggested, and it worked (of course!). It hadn't occured to me to save the workbooks. Thank you so much for your help and time. :) Jenno -- Jenno ------------------------------------------------------------------------ Jenno's Profile: http://www.excelforum.com/member.php...o&userid=26236 View this thread: http://www.excelforum.com/showthread...hreadid=397691 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating linked cells within a workbook, from worksheet to workshe | Excel Discussion (Misc queries) | |||
How do I save an Excel workbook so that cells referencing another. | Excel Discussion (Misc queries) | |||
Two spreadsheets linked - moving cells | Excel Discussion (Misc queries) | |||
instead of moving cells my arrow keys scroll the page | Excel Discussion (Misc queries) | |||
Moving a chart from one workbook to another | Charts and Charting in Excel |