Automatic range adjustment when copying data

[Sandeman]

Excel experts...

I have two worksheets in my workbook, one of which goes to a master worksheet and selects a range of cells. The macro I use is:

ActiveCell.FormulaR1C1 = "=Dates!RC"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A500"), Type:=xlFillDefault
Range("A2:A500").Select

I use a range from 2 to 500 because the information rarely exceeds 500 cells. How can I have the macro go to the main worksheet and only collect what data is there rather than just 500 rows of data, most of which I don't use and look ugly in the worksheet.

Many thanks for your help. This site and the people on it is a savior.

[Tom Ogilvy]

Dim rng as Range
With Worksheets("Dates")
set rng = .Range(.Cells(2,1),.Cells(2,1).End(xldown))
End with
with Worksheets("Master")
.Range(rng.Address).FormulaR1C1 = "=Dates!RC"
end with

--
Regards,
Tom Ogilvy


"Sandeman" wrote:

>
> Excel experts...
>
> I have two worksheets in my workbook, one of which goes to a master
> worksheet and selects a range of cells. The macro I use is:
>
> ActiveCell.FormulaR1C1 = "=Dates!RC"
> Range("A2").Select
> Selection.AutoFill Destination:=Range("A2:A500"),
> Type:=xlFillDefault
> Range("A2:A500").Select
>
> I use a range from 2 to 500 because the information rarely exceeds 500
> cells. How can I have the macro go to the main worksheet and only
> collect what data is there rather than just 500 rows of data, most of
> which I don't use and look ugly in the worksheet.
>
> Many thanks for your help. This site and the people on it is a savior.
>
>
> --
> Sandeman
> ------------------------------------------------------------------------
> Sandeman's Profile: http://www.excelforum.com/member.php...o&userid=32440
> View this thread: http://www.excelforum.com/showthread...hreadid=523117
>
>