Moving cells to another workbook sequentially

[Jenno]

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

[Dave Peterson]

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

[Jenno]

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

[Dave Peterson]

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

[Jenno]

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

[Dave Peterson]

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

[Jenno]

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