Inserting last row from one worksheet into next empty row in anoth

[Guest]

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

 

[Guest]

Application.CutCopyMode = True
ActiveSheet.Cells(1,1).End(xldown).offset(-1,0).EntireRow.Copy
Sheet1.Cells(1,1).End(xldown).Offset(1,0).EntireRow.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).

 

[Guest]

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.