Copy and Paste LAST ROW of data: non-contiguous Row, contiguous Column

[Sam via OfficeKB.com]

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

 

[Bill Renaud]

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.

 

[Sam via OfficeKB.com]

Hi Bill,

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

Cheers,
Sam

 

[Bill Renaud]

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

 

[Sam via OfficeKB.com]

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

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

 

[Bill Renaud]

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?

 

[Sam via OfficeKB.com]

Hi Bill,

Thanks for reply.

The first column, column"A", will have a few blank cells then the start of
the contiguous data; some blank cells then more data. I need to find the last
cell of data within the first block of contiguous cells in column "A" and
then copy that last row. The row of data to be copied will contain non-
contiguous data.

I would like to specify the sheets that I need to copy using an array.

Cheers,
Sam

<<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).

 

[Bill Renaud]

So, if I understand you correctly, your data looks like the following:

A B C D E
1 data data data data
2 data data data data
3 data data data data data
4 data data data data data
5 data data data data data
6 123 456 789 123
7 data data data data
8 456 123 345 678

.... and you want to copy row 6 (123, 456, etc.) to row 9 on specified
sheets (Sheet 1, Sheet3, etc.)?

 

[Bill Renaud]

Assuming the data structure I gave in my previous post, try the following
code:

'----------------------------------------------------------------------
Public Sub SpecialCopyRowAllSheets()
Const MsgBoxTitle = "Special Copy Row of Data"

Dim wsList As Variant
Dim ilngSheet As Long
Dim ws As Worksheet
Dim rngLastCellColA As Range
Dim rngNewRowColA As Range

On Error Resume Next

'Array of sheet names is 0 based. List the sheets you want.
wsList = Array("Sheet1", "Sheet3")

For ilngSheet = LBound(wsList) To UBound(wsList)
Set ws = ActiveWorkbook.Worksheets(wsList(ilngSheet))
If ws Is Nothing _
Then
MsgBox "Worksheet '" & wsList(ilngSheet) & "'" & vbNewLine & _
"does not exist in this workbook.", _
vbCritical + vbOKOnly, _
MsgBoxTitle
Else
Set rngLastCellColA = FindLastCellColA(ws)
If rngLastCellColA Is Nothing _
Then
MsgBox "Worksheet data on sheet 'Sheet1'" & vbNewLine & _
"does not fit the expected pattern." & vbNewLine & _
"Cannot copy data.", _
vbExclamation + vbOKOnly, _
MsgBoxTitle
Else
With ws.UsedRange
Set rngNewRowColA = ws.Cells(.Row + .Rows.Count, 1)
End With

'Copy row of data to new, empty row at the bottom.
rngLastCellColA.EntireRow.Copy Destination:=rngNewRowColA
End If
End If
Set ws = Nothing 'Required, in case next sheet does not exist.
Next ilngSheet
End Sub

'----------------------------------------------------------------------
Private Function FindLastCellColA(ws As Worksheet) As Range
Dim rngCellA1 As Range
Dim rngUsedRange As Range
Dim rngLastCellColA As Range

Set rngCellA1 = ws.Range("A1")
Set rngUsedRange = ws.UsedRange

'Find last cell of contiguous data in Column $A.
If IsEmpty(rngCellA1) _
Then
'Do Ctrl+Down twice to reach the last row of contiguous data.
Set rngLastCellColA = rngCellA1.End(xlDown).End(xlDown)
Else
'Do Ctrl+Down only once to reach the last row of contiguous data.
Set rngLastCellColA = rngCellA1.End(xlDown)
End If

If Intersect(rngLastCellColA, rngUsedRange) = rngLastCellColA _
Then
Set FindLastCellColA = rngLastCellColA
Else
Set FindLastCellColA = Nothing
End If
End Function