Newbie Question-Pasting Multi-Row Data into Accumulating List With Macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tallen - 27 Nov 2006 13:50 GMT

I have a two page worksheet. On the first page, data is entered into a 7
row by 6 column table. Several computations are done on all the data
and a weekly report can be printed. I want to accumulate that weekly
data in a list on the second page using a macro. I have recorded a
macro that will copy all the current week's entries from the first page
and "paste special" the data values onto the second page with a single
click on a button located on the first page. What I can not figure out
is how to paste the subsequent week's data below the previous set of
data. Because some of the computations are done for all the data, I can
not copy/paste on a daily basis nor do I want any formulas on the second
page.

I need to modify the recorded macro to somehow find the next blank cell
in the "a" column and then paste all 7 rows at one time. I'm a newbie
on macros and VBA and this has my eyes crossed!

Thanks in advance for any advice.

Reply to this Message

Bob Phillips - 27 Nov 2006 13:55 GMT

Set start_range = Worksheets("Sheet2").Range("A1").End(xlDown)
Worksheets("Sheet1").Range("A1:F7").Copy start_range

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

> I have a two page worksheet. On the first page, data is entered into a 7
> row by 6 column table. Several computations are done on all the data
[quoted text clipped - 13 lines]
>
> Thanks in advance for any advice.

Reply to this Message

Dave Peterson - 27 Nov 2006 14:31 GMT

I bet Bob wanted to come down one more row:

Set start_range = Worksheets("Sheet2").Range("A1").End(xlDown).offset(1,0)
Worksheets("Sheet1").Range("A1:F7").Copy start_range

He started at the top and came down to find the next available cell.

Another way is to start at the bottom and come up:

dim DestCell as range
with worksheets("Sheet2")
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

whateverrangetocopy.copy _
destination:=destcell

> Set start_range = Worksheets("Sheet2").Range("A1").End(xlDown)
> Worksheets("Sheet1").Range("A1:F7").Copy start_range
[quoted text clipped - 24 lines]
> >
> > Thanks in advance for any advice.

Signature

Dave Peterson

Reply to this Message

tallen - 28 Nov 2006 12:51 GMT

> I have a two page worksheet...

Thanks guys. I'll work on this today and see where it leads me.

T

Reply to this Message

Rate this thread: