Moving cells to another workbook sequentially

  • Thread starter Thread starter Jenno
  • Start date Start date
J

Jenno

Hi

Hope someone can help. I need a macro to enable moving two individua
cells from one workbook to another workbook. In the first shee
*(Daily)* the cells will always be entered on the same row. A summar
will be kept each time the row has different data written to it (ever
day) on a second sheet *(Summary), * so they need to be positioned ther
sequentially.

eg cells b4 and d4 in workbook Daily need a macro to be moved to th
Summary workbook - first time to cells f2 and g2 - then next time t
row beneath and so on each time macro is run. Hope this makes sense!

Thanks Jenn
 
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
 
Thanks for your help Dave, the only trouble is when I run this I get
run time *error 9 subscript out of range*. Is that something simple
can fix? Sorry for the delay with this but today is the firs
opportunity I have had to test this.

Jenn
 
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.
 
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, deletin
the Option Explicit line and extra end sub as generated by th
program.

Jenno :confused
 
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.
 
Hi Dave

Just tried out what you suggested, and it worked (of course!). I
hadn't occured to me to save the workbooks. Thank you so much for you
help and time. :)

Jenn
 
Back
Top