Hi All,

I would like a solution to copy the last row of data on multiple worksheets
(within the same workbook) to the next blank row of the individual worksheet
(Sheet1 copy last row of data to next blank row on Sheet1, Sheet3 copy last
row of data to next blank row on Sheet3 etc). The sheets will not be
sequentially named and the number of rows on each sheet will vary.

The row of data will be non-contiguous (one or more blank cells) in the row
and it will contain constants as well as formulae; the column will be
contiguous data, apart from a few blank rows before the start of the
contiguous column data.

Thanks
Sam

--
Message posted via http://www.officekb.com

Sounds like you are entering data in a list. I would just use Data|Form to
enter a new row of data. All cells above the new row that have formulas
will automatically be copied down. To copy a cell from the row above that
has a constant in it, press Ctrl+; (Ctrl+ semicolon) while in that field.

--
Regards,
Bill Renaud

Hi Bill,

Thank you for reply. Because of the numerous worksheets involved a VBA
solution would be more suitable.

Cheers,
Sam

Bill Renaud wrote:
Sounds like you are entering data in a list. I would just use Data|Form to
enter a new row of data. All cells above the new row that have formulas
will automatically be copied down. To copy a cell from the row above that
has a constant in it, press Ctrl+; (Ctrl+ semicolon) while in that field.

--
Message posted via http://www.officekb.com

The following routine will copy the last row on ALL worksheets in the
active workbook, so you can put this macro in any workbook you want and use
it across multiple workbooks. It copies the entire cell contents, formulas,
formats, comments and all.

Public Sub CopyLastRowAllSheets()
Dim ws As Worksheet
Dim rngLastRow As Range

For Each ws In ActiveWorkbook.Worksheets
With ws.UsedRange
Set rngLastRow = .Resize(1).Offset(.Rows.Count - 1)
End With

With rngLastRow
.Copy Destination:=.Offset(1)
End With
Next ws
End Sub

--
Regards,
Bill Renaud

Hi Bill,

Thank you very much for further assistance.

Your treatment of the rows is fine. Unfortunately, I have a problem with the
column. I require a copy process that can copy a column of contiguous data,
that has a few blank rows before the start of the contiguous column data and
excludes any data after the contiguous block separated by a blank cell in the
column (data below the contiguous block separated by blank cells should not
be copied).

Also, only specific worksheets in the workbook should be copied, not all.

Further assistance very much appreciated.

Cheers,
Sam

Bill Renaud wrote:
The following routine will copy the last row on ALL worksheets in the
active workbook, so you can put this macro in any workbook you want and use
it across multiple workbooks. It copies the entire cell contents, formulas,
formats, comments and all.

Public Sub CopyLastRowAllSheets()
Dim ws As Worksheet
Dim rngLastRow As Range

For Each ws In ActiveWorkbook.Worksheets
With ws.UsedRange
Set rngLastRow = .Resize(1).Offset(.Rows.Count - 1)
End With

With rngLastRow
.Copy Destination:=.Offset(1)
End With
Next ws
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200711/1

Sam wrote:
<<I require a copy process that can copy a column of contiguous data, that
has a few blank rows before the start of the contiguous column data and
excludes any data after the contiguous block separated by a blank cell in
the column (data below the contiguous block separated by blank cells should
not be copied).

Also, only specific worksheets in the workbook should be copied, not all.

Can you be more specific about what you need?

--
Regards,
Bill Renaud