Please help!
I am trying to do a number of different things in a single macro and can't
seem to make it work.

1) I need to copy a data range (that vaies in length and width) that begins
in cell B20 in Sheet 1.
2) The copied data then needs to be pasted (as values) into Sheet 2 into the
first available row in column A. ...by first available row, I mean the the
first row that does not contain data.
3) The original copied data (from #1) needs to be cleared, but the formulas
must remain.

Any assitance would be greatly appreciated!

dim rng as Range
Set rng = .Range(.Cells(65536,1),.Cells(65536,1)).End (xlUp)
rng.Select

This should give the first available cell in Column A.
--
Best wishes,

Jim


"ToddEZ" wrote:

> Please help!
> I am trying to do a number of different things in a single macro and can't
> seem to make it work.
>
> 1) I need to copy a data range (that vaies in length and width) that begins
> in cell B20 in Sheet 1.
> 2) The copied data then needs to be pasted (as values) into Sheet 2 into the
> first available row in column A. ...by first available row, I mean the the
> first row that does not contain data.
> 3) The original copied data (from #1) needs to be cleared, but the formulas
> must remain.
>
> Any assitance would be greatly appreciated!

I left out an important part.

With Cells
dim rng as Range
Set rng = .Range(.Cells(65536,1),.Cells(65536,1)).End (xlUp)
rng.Select
End With

--
Best wishes,

Jim


"ToddEZ" wrote:

> Please help!
> I am trying to do a number of different things in a single macro and can't
> seem to make it work.
>
> 1) I need to copy a data range (that vaies in length and width) that begins
> in cell B20 in Sheet 1.
> 2) The copied data then needs to be pasted (as values) into Sheet 2 into the
> first available row in column A. ...by first available row, I mean the the
> first row that does not contain data.
> 3) The original copied data (from #1) needs to be cleared, but the formulas
> must remain.
>
> Any assitance would be greatly appreciated!

Now that I have "digested" the entire message:

1.
dim rng as Range
With Cells
Set rng1 = .Range(.Cells(20, 2), .Cells(20, 2).End(xlDown))
Set rng2 = .Range(.Cells(20, 2), .Cells(20, 2).End(xlToRight))
Set rng = Range(rng1, rng2)
rng.Select
Selection.Copy


2.
Sheets("Sheet2").activate
dim rng as Range
Set rng = .Range(.Cells(65536,1),.Cells(65536,1)).End (xlUp)
rng.Select
End With

3.

I'm not clear on what you need here.


--
Best wishes,

Jim


"ToddEZ" wrote:

> Please help!
> I am trying to do a number of different things in a single macro and can't
> seem to make it work.
>
> 1) I need to copy a data range (that vaies in length and width) that begins
> in cell B20 in Sheet 1.
> 2) The copied data then needs to be pasted (as values) into Sheet 2 into the
> first available row in column A. ...by first available row, I mean the the
> first row that does not contain data.
> 3) The original copied data (from #1) needs to be cleared, but the formulas
> must remain.
>
> Any assitance would be greatly appreciated!