Find last row macro problem

[Ryk]

I am using the following macro (it is one of three that are all the
same but point to different Month Sheets) to do two jobs at once, and
the second part is acting oddly when copying from the Suggestions sheet
and moving it into the Suggested Changes sheet. The first part copies
to its month sheet just fine, but I want the suggestions copied too one
sheet and to be one after the other. A1-A20 have values in my test
run, so the effect if I run this twice, is I should see the same 20
lines of data one after the other, but I do not, the first run puts the
data perfectly at the top starting at A1, but running it a second time
places the next data starting in A996 not A21 as I think it should.
Now if it is because I grab A1-Z1000 to copy, can I set it in the macro
to only copy the used rows on suggestions sheet and correct this?

Any ideas please?



Sub copy2()
'
' copy1 Macro
' Macro recorded 8/22/2006 by ryk
'
' Keyboard Shortcut: Ctrl+t
'
Range("A4:AX1006").Select
Selection.Copy
Sheets("Month Two").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Month Two").Select
Range("A1").Select
Selection.Copy
Sheets("Month Two").Select
Range("A1").Select
ActiveSheet.Paste
Range("B6:B1006").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Sheets("Suggestions").Select
Range("A5:Z1000").Select
Selection.Copy
Sheets("Suggested Changes").Select
Range("A1").Select
mycell = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & mycell).Select
Selection.PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A" & mycell).Select


End Sub


Thanks for any help given...

Ryk

 

[Dave Peterson]

This kind of thing finds the last row with something in it:

mycell = Cells(Rows.Count, "A").End(xlUp).Row

So if you do this manually, go to the correct sheet, then A65536, then hit the
End key, followed by the up arrow button, where do you stop?

There could be a couple of problems that I'm guessing.

#1. You have formulas that evaluate to "" in those cells. Your code will still
find that last used cell in the column--and used includes any formula--no matter
what it evaluates to.

#2. The other problem is that if you used to have formulas in those cells that
evaluated to "", and you did an edit|Copy, then Edit|paste special|values, then
excel doesn't see those cells as empty either.

Your End|Up Arrow will stop on one of those cells, too.

Could either of those be the problem?

If it's the second guess, you can use this technique to clean up those cells.

Select the range
edit|replace
what: (leave empty)
with: $$$$$
replace all
then
edit|replace
what: $$$$$
with: (leave blank)
replace all

If you record a macro when you do this, you can add that code right after the
pastespecial|Values lines in your code.

Then the .end(xlup).row should return the correct row.