Dynamic range

[Guest]

Dear Experts:

I have the following sheets:

Sheet1 cosnsits of Summary
Sheet 2,3,4... the data extract from accounting software per department
I created a macro to put certain data to Sheet1 and it works prefectly

My question is how can we make a macro that can work eventhough the number
of rows ( range) from data sheets could change anytime.

When I noticed the recording macro it copied to Range "A9", the starting
cell in Summary Sheet, it went down to 20 lines from data sheet1, and the
next it to Range "A 29" if next time it could be 4o lines from data sheet1,
the macro reslut could be wrong because it stil points to Range "A29".

Please help, should we used dynamic copied range from the datasheets?,
bedause in Summary we can make it always go to A9 then End.xldown goes to the
empy cell, something like this?

Frank

 

[Guest]

Try something like this:

Dim myrange As Range

Set myrange = Worksheets("Sheet1").Cells(1, 1)
lrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Set myrange = myrange.Resize(lrow - myrange.Row + 1, 1)
Debug.Print myrange.Address

 

[Guest]

hi,
here's one method that works;

Sub MacSumRange()

Range("A1", Range("A1").End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="Nam1", RefersToR1C1:=Selection
Range("A1").End(xlDown).Offset(1, 0).Formula = "=sum(Nam1)"

End Sub

there are others.

Regards
FSt1

 

[Guest]

Sounds verygood, what is MacSumRange for, is this for Maccintosh Computer?, I
have Windows PC

What are the others Sir, is the VBA used in the Summary?or for data sheet.

Thanks in advance.
Frank,
Jakarta, Indonesia.

 

[Guest]

Hi Barb,

Thanks for your help, it gives me more light to know more about this Excel
VBA. Actually, how do you know that this method is one of the solution to my
problem. Actually my field is Finance and Accounting, so I just selfstudy on
this. I just try to use my logic by reading the help of this excel.

In my case here, there are many sheets contains data that should be combined
to summary, and the problem is number of data/rows could change anytime ( say
twice a month).

Appreciate if you could explain more...., What does Irow
mean=Sheets("Sheet1")....

Thanks in advance

Frank
Jakarta/Indonesia