Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting last row from one worksheet into next empty row in anoth
Hello,
I am currently working on a project in Excel 2003 in which I need to have other pages send the last updated row into a new row on another worksheet, called the master worksheet. I have searched the forums, and am currently having this going. Application.CutCopyMode = True ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .EntireRow.Copy Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Offs et(1, 0).EntireRow.PasteSpecial Application.CutCopyMode = False The 'application.cutcopymode' piece of code in the beginning and end is just to prevent from the marching ants outline from sticking. This piece of code works, BUT, let's say I need to delete something of the master page, in my current case, test data in the cells, in the future, data mistyped, etc., it keeps going even if I deleted the previous row (ex., the master page gets update with rows 1, 2, 3, then I delete 3, but the next time it still updates to 4 skipping the empty row 3). i also have a new problem with this code, I recently added a total row on the bottom to add up certain values on both the master and the other worksheets, but now that I have it, this code will only copy the total results. Any help would be gretly appreciated, even if it's just a nudge in the right direction. Thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting last row from one worksheet into next empty row in anoth
Application.CutCopyMode = True ActiveSheet.Cells(1,1).End(xldown).offset(-1,0).EntireRow.Copy Sheet1.Cells(1,1).End(xldown).Offset(1,0).EntireRo w.PasteSpecial Application.CutCopyMode = False This assumes there are no empty rows embedded in your data and the sum prow on the activesheet is contiguous with the data rows (no blank row in between). -- Regards, Tom Ogilvy "Dale Schwer" wrote: Hello, I am currently working on a project in Excel 2003 in which I need to have other pages send the last updated row into a new row on another worksheet, called the master worksheet. I have searched the forums, and am currently having this going. Application.CutCopyMode = True ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .EntireRow.Copy Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Offs et(1, 0).EntireRow.PasteSpecial Application.CutCopyMode = False The 'application.cutcopymode' piece of code in the beginning and end is just to prevent from the marching ants outline from sticking. This piece of code works, BUT, let's say I need to delete something of the master page, in my current case, test data in the cells, in the future, data mistyped, etc., it keeps going even if I deleted the previous row (ex., the master page gets update with rows 1, 2, 3, then I delete 3, but the next time it still updates to 4 skipping the empty row 3). i also have a new problem with this code, I recently added a total row on the bottom to add up certain values on both the master and the other worksheets, but now that I have it, this code will only copy the total results. Any help would be gretly appreciated, even if it's just a nudge in the right direction. Thank you |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting last row from one worksheet into next empty row in a
Hi Dale, When you code with UsedRange, Excel finds the furthermost cell to
the right and the furthermost cell down with data of any kind in them, including data invisible to the casual observer, and uses the column and row for those two cells as the outer bounds of the used range. What this means is, that you can have blank rows and columns within that boundary. So you need to use caution when coding with UsedRange to find the last row. As Tom has demonstrated with the code he posted, using the End(xlDown).Row code will travel the length of a group of rows so long as they all have data in them, or will go from the last row containin data across a group of blank rows to the next row with data, depending on where the pointer is when the command is executed. You can also find the last row by going to the bottom and coming back up with: LastRow = Cells(Rows.Count, "ColOfChoice").End(xlUp).Row Insert the Column number in "ColOfChoice" for the one you know contains data all the way down to the bottom of your data range. Good Luck. "Tom Ogilvy" wrote: Application.CutCopyMode = True ActiveSheet.Cells(1,1).End(xldown).offset(-1,0).EntireRow.Copy Sheet1.Cells(1,1).End(xldown).Offset(1,0).EntireRo w.PasteSpecial Application.CutCopyMode = False This assumes there are no empty rows embedded in your data and the sum prow on the activesheet is contiguous with the data rows (no blank row in between). -- Regards, Tom Ogilvy "Dale Schwer" wrote: Hello, I am currently working on a project in Excel 2003 in which I need to have other pages send the last updated row into a new row on another worksheet, called the master worksheet. I have searched the forums, and am currently having this going. Application.CutCopyMode = True ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .EntireRow.Copy Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Offs et(1, 0).EntireRow.PasteSpecial Application.CutCopyMode = False The 'application.cutcopymode' piece of code in the beginning and end is just to prevent from the marching ants outline from sticking. This piece of code works, BUT, let's say I need to delete something of the master page, in my current case, test data in the cells, in the future, data mistyped, etc., it keeps going even if I deleted the previous row (ex., the master page gets update with rows 1, 2, 3, then I delete 3, but the next time it still updates to 4 skipping the empty row 3). i also have a new problem with this code, I recently added a total row on the bottom to add up certain values on both the master and the other worksheets, but now that I have it, this code will only copy the total results. Any help would be gretly appreciated, even if it's just a nudge in the right direction. Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting data to next available empty cells | Excel Discussion (Misc queries) | |||
Macro needed to pull data from one worksheet and enter it in anoth | Excel Discussion (Misc queries) | |||
Same Worksheet, want to transfer information from one tab to anoth | Excel Worksheet Functions | |||
How do I set up formula to copy format from one worksheet to anoth | New Users to Excel | |||
Is it possible to copy a header/footer from one worksheet to anoth | Excel Discussion (Misc queries) |