Hi all,
I am struggling with my VBA code at the moment trying to automate the task
of collecting data from several worksheets and placing it in one worksheet to
form one data Worksheet with all of the information. Every source Worksheet
has the same top header row record names but the number of records/rows in
each source Worksheet varies. Ideally the merged data would also sort into
order according to the content of column A or 1 in R1C1 reference.

Thanks for your time.
Geoff
England

The code to select sheets, copy, paste and sort can all be recorded, with the
caveat that you need to determine which rows to copy from each sheet, and
where to paste on the combined sheet. Here's a snippet that will determine
the final non-blank cell in column A of the active worksheet:
Dim LastRow As Long
Range("A65536").Select
Selection.End(xlUp).Select
LastRow = Selection.Row
MsgBox ("Final row of data is " & LastRow)
You can then use the LastRow variable to select the rows to copy (rows("2:"
& lastrow).select) from each separate worksheet and where to paste (range("A"
& lastrow+1).select) to the combined sheet. --Bruce


"Coconuts" wrote:

Hi all,
I am struggling with my VBA code at the moment trying to automate the task
of collecting data from several worksheets and placing it in one worksheet to
form one data Worksheet with all of the information. Every source Worksheet
has the same top header row record names but the number of records/rows in
each source Worksheet varies. Ideally the merged data would also sort into
order according to the content of column A or 1 in R1C1 reference.

Thanks for your time.
Geoff
England

Hi Bruce,

thanks for that. I knew the lasts row coding but the rest is a useful step
forward. I will work on it

thanks again
Geoff

"bpeltzer" wrote:

The code to select sheets, copy, paste and sort can all be recorded, with the
caveat that you need to determine which rows to copy from each sheet, and
where to paste on the combined sheet. Here's a snippet that will determine
the final non-blank cell in column A of the active worksheet:
Dim LastRow As Long
Range("A65536").Select
Selection.End(xlUp).Select
LastRow = Selection.Row
MsgBox ("Final row of data is " & LastRow)
You can then use the LastRow variable to select the rows to copy (rows("2:"
& lastrow).select) from each separate worksheet and where to paste (range("A"
& lastrow+1).select) to the combined sheet. --Bruce


"Coconuts" wrote:

Hi all,
I am struggling with my VBA code at the moment trying to automate the task
of collecting data from several worksheets and placing it in one worksheet to
form one data Worksheet with all of the information. Every source Worksheet
has the same top header row record names but the number of records/rows in
each source Worksheet varies. Ideally the merged data would also sort into
order according to the content of column A or 1 in R1C1 reference.

Thanks for your time.
Geoff
England