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

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

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

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

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 :confused:


--
Jenno
------------------------------------------------------------------------
Jenno's Profile: http://www.excelforum.com/member.php...o&userid=26236
View this thread: http://www.excelforum.com/showthread...hreadid=397691

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 :confused:

--
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

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