Hello All,

I wonder if you can help with this (probably simple) problem.


Each worksheet Im working on may have several hundred rows, though each
one a different number of rows.
Im compiling them all to one worksheet.

The columns used, however, are set. So I'm trying to make a macro to
Select all the columns but only as far down as the data goes.

I'll need a similar (probably) macro to search down in the "Combined"
worksheet, find the first free row and ValuePaste the data in there.

Ive got as far as realising it may be something to do with:

Range("A:M").Select
Range(Selection, Selection.End(xlDown)).Select

But thats as far as Ive got.

Any help much appreciated.

Thanks
Ed



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Perhaps I have to use the R1C1 code, which Ive not looked at yet?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

One way:

If column A will have a value in each record, then this will copy
all the records on sheet1 to the next available row on sheet2:

With Worksheets("Sheet1")
.Range("A1:M" & .Range("A" & Rows.Count).End(xlUp).Row).Copy _
Destination:=Worksheets("Sheet2").Range("A" & _
End With

This technique has XL look "up" from the bottom row to find the last
row with data.

Note that you almost never need to select or activate a range in
order to work with it. Using the range object directly makes your
code smaller, faster and, IMO, easier to maintain.


In article ,
universal wrote:

Hello All,

I wonder if you can help with this (probably simple) problem.


Each worksheet Im working on may have several hundred rows, though each
one a different number of rows.
Im compiling them all to one worksheet.

The columns used, however, are set. So I'm trying to make a macro to
Select all the columns but only as far down as the data goes.

I'll need a similar (probably) macro to search down in the "Combined"
worksheet, find the first free row and ValuePaste the data in there.

Ive got as far as realising it may be something to do with:

Range("A:M").Select
Range(Selection, Selection.End(xlDown)).Select

But thats as far as Ive got.

Any help much appreciated.

I think J.E.'s code got truncated somehow:

With Worksheets("Sheet1")
.Range("A1:M" & .Range("A" & Rows.Count).End(xlUp).Row).Copy _
Destination:=Worksheets("Sheet2").Range("A" & _
Rows.Count).End(xlUp).Offset(1, 0)
End With

Might be what he really meant.


"J.E. McGimpsey" wrote:

One way:

If column A will have a value in each record, then this will copy
all the records on sheet1 to the next available row on sheet2:

With Worksheets("Sheet1")
.Range("A1:M" & .Range("A" & Rows.Count).End(xlUp).Row).Copy _
Destination:=Worksheets("Sheet2").Range("A" & _
End With

This technique has XL look "up" from the bottom row to find the last
row with data.

Note that you almost never need to select or activate a range in
order to work with it. Using the range object directly makes your
code smaller, faster and, IMO, easier to maintain.

In article ,
universal wrote:

Hello All,

I wonder if you can help with this (probably simple) problem.


Each worksheet Im working on may have several hundred rows, though each
one a different number of rows.
Im compiling them all to one worksheet.

The columns used, however, are set. So I'm trying to make a macro to
Select all the columns but only as far down as the data goes.

I'll need a similar (probably) macro to search down in the "Combined"
worksheet, find the first free row and ValuePaste the data in there.

Ive got as far as realising it may be something to do with:

Range("A:M").Select
Range(Selection, Selection.End(xlDown)).Select

But thats as far as Ive got.

Any help much appreciated.

--

Dave Peterson

Yup - don't know what happened. Thanks for completing the code,
Dave.

In article ,
Dave Peterson wrote:

I think J.E.'s code got truncated somehow:

With Worksheets("Sheet1")
.Range("A1:M" & .Range("A" & Rows.Count).End(xlUp).Row).Copy _
Destination:=Worksheets("Sheet2").Range("A" & _
Rows.Count).End(xlUp).Offset(1, 0)
End With

Might be what he really meant.