Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 31st 07, 11:12 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 247
Default Copy and Paste LAST ROW of data: non-contiguous Row, contiguous Column

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


  #2   Report Post  
Old November 1st 07, 05:45 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 417
Default Copy and Paste LAST ROW of data: non-contiguous Row, contiguous Column

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



  #3   Report Post  
Old November 2nd 07, 02:57 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 247
Default Copy and Paste LAST ROW of data: non-contiguous Row, contiguous Column

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

  #4   Report Post  
Old November 2nd 07, 06:20 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 417
Default Copy and Paste LAST ROW of data: non-contiguous Row, contiguous Column

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



  #5   Report Post  
Old November 4th 07, 01:40 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 247
Default Copy and Paste LAST ROW of data: non-contiguous Row, contiguous Column

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



  #6   Report Post  
Old November 4th 07, 08:03 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 417
Default Copy and Paste LAST ROW of data: non-contiguous Row, contiguous Column

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



  #7   Report Post  
Old November 4th 07, 11:53 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 247
Default Copy and Paste LAST ROW of data: non-contiguous Row, contiguous Column

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

Bill Renaud 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?


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

  #8   Report Post  
Old November 5th 07, 07:13 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 417
Default Copy and Paste LAST ROW of data: non-contiguous Row, contiguous Column

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

--
Regards,
Bill Renaud



  #9   Report Post  
Old November 5th 07, 08:18 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 417
Default Copy and Paste LAST ROW of data: non-contiguous Row, contiguous Column

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

--
Regards,
Bill Renaud





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy and Paste LAST ROW of data non-contiguous Sam via OfficeKB.com Excel Programming 6 October 29th 07 03:28 PM
xldown in a non-contiguous column David Gerstman Excel Programming 7 November 8th 06 06:25 PM
Paste Data into Contiguous (Visible) Cells trev_sk8r New Users to Excel 1 June 16th 06 10:04 PM
add all contiguous numbers-column-row Bernard Liengme New Users to Excel 0 May 2nd 06 02:02 PM
copy formulas from a contiguous range to a safe place and copy them back later Lucas Budlong Excel Programming 2 February 22nd 06 09:26 PM


All times are GMT +1. The time now is 04:09 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017