Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data non-contiguous
Hi All,
In a previous Thread: "Copy and Paste LAST ROW of data", Tom Ogilvy and Mike Fogleman both provided me with Great VB code. Tom's code copies the last row of (contiguous) data to the next blank row below, across multiple worksheets. Tom Ogilvy Sub CopyLast() Dim r1 as Range, r2 as Range Dim sh as worksheet for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) set r1 = sh.Columns(1).specialCells(xlConstants,xlNumbers). Areas(1) set r1 = r1(r1.count) if isempty(r1(1,2)) then set r2 = r1 else set r2 = r1.end(xltoRight) end if Range(r1,r2).Copy r1(2) Next sh End Sub If possible, I would like to adjust the code, still copying the last row of data BUT the data will be non-contiguous (one or more blank cells) in the row and it will contain constants as well as formulae. ------------------------------------------------------------------- Mike Fogleman also provided this treasu Is there other data surrounding this contiguous data that we need to avoid? If not, then this simple code will do it: Sub test() Dim LRow As Long LRow = Cells(Rows.Count, 1).End(xlUp).Row Rows(LRow).Copy Rows(LRow + 1) End Sub I added the following lines to Mike's code but could not get it to goto each worksheet in turn. Dim sh as worksheet for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) Next sh Help very much appreciated. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200710/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data non-contiguous
Sub test()
Dim LRow As Long LRow = Cells(Rows.Count, 1).End(xlUp).Row Rows(LRow).Copy Rows(LRow + 1) End Sub I added the following lines to Mike's code but could not get it to goto each worksheet in turn. Dim sh as worksheet for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) LRow = sh.Cells(Rows.Count, 1).End(xlUp).Row with sheets("Sheet2") .Rows("1:10").Copy Destination:=sh.Rows(LRow + 1) end with Next sh "Sam via OfficeKB.com" wrote: Hi All, In a previous Thread: "Copy and Paste LAST ROW of data", Tom Ogilvy and Mike Fogleman both provided me with Great VB code. Tom's code copies the last row of (contiguous) data to the next blank row below, across multiple worksheets. Tom Ogilvy Sub CopyLast() Dim r1 as Range, r2 as Range Dim sh as worksheet for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) set r1 = sh.Columns(1).specialCells(xlConstants,xlNumbers). Areas(1) set r1 = r1(r1.count) if isempty(r1(1,2)) then set r2 = r1 else set r2 = r1.end(xltoRight) end if Range(r1,r2).Copy r1(2) Next sh End Sub If possible, I would like to adjust the code, still copying the last row of data BUT the data will be non-contiguous (one or more blank cells) in the row and it will contain constants as well as formulae. ------------------------------------------------------------------- Mike Fogleman also provided this treasu Is there other data surrounding this contiguous data that we need to avoid? If not, then this simple code will do it: Sub test() Dim LRow As Long LRow = Cells(Rows.Count, 1).End(xlUp).Row Rows(LRow).Copy Rows(LRow + 1) End Sub I added the following lines to Mike's code but could not get it to goto each worksheet in turn. Dim sh as worksheet for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) Next sh Help very much appreciated. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200710/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data non-contiguous
Hi Joel,
Thank you very much for reply and assistance. Dim LRow As Long Dim sh as worksheet for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) LRow = sh.Cells(Rows.Count, 1).End(xlUp).Row with sheets("Sheet2") .Rows("1:10").Copy Destination:=sh.Rows(LRow + 1) end with Next sh End Sub The above does not produce the desired result. The sheets will not be sequentially named and the number of rows on each sheet will vary. Further help appreciated. Cheers, Sam Joel wrote: Sub test() Dim LRow As Long LRow = Cells(Rows.Count, 1).End(xlUp).Row Rows(LRow).Copy Rows(LRow + 1) End Sub I added the following lines to Mike's code but could not get it to goto each worksheet in turn. Dim sh as worksheet for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) LRow = sh.Cells(Rows.Count, 1).End(xlUp).Row with sheets("Sheet2") .Rows("1:10").Copy Destination:=sh.Rows(LRow + 1) end with Next sh -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data non-contiguous
What this code will do is copy the last row of sheet2 to the bottom of all
the sheets listed in the Array in the code below. Array can be in any order and contain as many sheets as necessary (except sheet 2 cannot be in the arrray). Dim LRow As Long Dim sh as worksheet with sheets("Sheet2") Sh2LRow = .Cells(Rows.Count, "A").End(xlUp).Row end with for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) shLRow = sh.Cells(Rows.Count, "A").End(xlUp).Row with sheets("Sheet2") .Rows(Sh2LRow).Copy Destination:=sh.Rows(shLRow + 1) end with Next sh End Sub "Sam via OfficeKB.com" wrote: Hi Joel, Thank you very much for reply and assistance. Dim LRow As Long Dim sh as worksheet for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) LRow = sh.Cells(Rows.Count, 1).End(xlUp).Row with sheets("Sheet2") .Rows("1:10").Copy Destination:=sh.Rows(LRow + 1) end with Next sh End Sub The above does not produce the desired result. The sheets will not be sequentially named and the number of rows on each sheet will vary. Further help appreciated. Cheers, Sam Joel wrote: Sub test() Dim LRow As Long LRow = Cells(Rows.Count, 1).End(xlUp).Row Rows(LRow).Copy Rows(LRow + 1) End Sub I added the following lines to Mike's code but could not get it to goto each worksheet in turn. Dim sh as worksheet for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) LRow = sh.Cells(Rows.Count, 1).End(xlUp).Row with sheets("Sheet2") .Rows("1:10").Copy Destination:=sh.Rows(LRow + 1) end with Next sh -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data non-contiguous
Hi Joel,
Thank you for reply. I actually need the last row of each sheet in the array to be copied to its own sheet (the same sheet) but to the next empty row below. Further help appreciated. Cheers, Sam Joel wrote: What this code will do is copy the last row of sheet2 to the bottom of all the sheets listed in the Array in the code below. Array can be in any order and contain as many sheets as necessary (except sheet 2 cannot be in the arrray). Dim LRow As Long Dim sh as worksheet with sheets("Sheet2") Sh2LRow = .Cells(Rows.Count, "A").End(xlUp).Row end with for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) shLRow = sh.Cells(Rows.Count, "A").End(xlUp).Row with sheets("Sheet2") .Rows(Sh2LRow).Copy Destination:=sh.Rows(shLRow + 1) end with Next sh End Sub -- Message posted via http://www.officekb.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data non-contiguous
Sub CopyLast()
Dim LRow As Long Dim sh As Worksheet For Each sh In Worksheets(Array("sheet1", "sheet3", "sheet5")) LRow = sh.Cells(Rows.Count, "A").End(xlUp).Row sh.Rows(LRow).Copy Destination:=sh.Rows(LRow + 1) Next sh End Sub "Sam via OfficeKB.com" wrote: Hi Joel, Thank you for reply. I actually need the last row of each sheet in the array to be copied to its own sheet (the same sheet) but to the next empty row below. Further help appreciated. Cheers, Sam Joel wrote: What this code will do is copy the last row of sheet2 to the bottom of all the sheets listed in the Array in the code below. Array can be in any order and contain as many sheets as necessary (except sheet 2 cannot be in the arrray). Dim LRow As Long Dim sh as worksheet with sheets("Sheet2") Sh2LRow = .Cells(Rows.Count, "A").End(xlUp).Row end with for each sh in worksheets(Array("sheet1", "sheet3", "sheet5")) shLRow = sh.Cells(Rows.Count, "A").End(xlUp).Row with sheets("Sheet2") .Rows(Sh2LRow).Copy Destination:=sh.Rows(shLRow + 1) end with Next sh End Sub -- Message posted via http://www.officekb.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste LAST ROW of data non-contiguous
Hi Joel,
Thank you very much for further assistance. Your code does the job very well; very much appreciated. Cheers, Sam Joel wrote: Sub CopyLast() Dim LRow As Long Dim sh As Worksheet For Each sh In Worksheets(Array("sheet1", "sheet3", "sheet5")) LRow = sh.Cells(Rows.Count, "A").End(xlUp).Row sh.Rows(LRow).Copy Destination:=sh.Rows(LRow + 1) Next sh End Sub -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How copy none excel data & paste in 2007 without overwriting data | Excel Discussion (Misc queries) | |||
filted data, copy and paste a col. puts data in wrong row how fix | New Users to Excel | |||
Paste Data into Contiguous (Visible) Cells | New Users to Excel | |||
copy formulas from a contiguous range to a safe place and copy them back later | Excel Programming | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) |