end(xlup) cant work

[TheAnswer]

Hello, my Range("A65536").End(xlUp).Select statement cant seem to work. When i execute the If and Else statement again, the copy and paste function will work but it will overwrite the previous informations that i had pasted.

Appreciate all helps and thanks in advance.



If subArea = "SubTxn" Then
Sheets("Vol").Activate
subArea.EntireRow.Select
Selection.Copy
Sheets("CannotFind").Activate
Range("A65536").End(xlUp).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial
Selection.Offset(1, 0).Select

ElseIf subArea <> "SubTxn" Then
Sheets("Vol").Activate
subArea.Select
Selection.Resize(Selection.Rows.Count + 3, Selection.Columns.Count + 0).Select
Selection.EntireRow.Select
Selection.Copy
Sheets("CannotFind").Activate
Range("A65536").End(xlUp).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial
Selection.Offset(4, 0).Select

End If
End If

[shrivallabha]

Looks to me you are using recorded code. Try using the syntax below:
subArea.EntireRow.Copy
ActiveSheet.Paste Destination:= Sheets("CannotFind").Range("A65536").End(xlUp).Offset(1,0)
Application.CutCopyMode = False

[bubbapost]

You might want to try this:

Replacing "Range("A65536").End(xlUp).Select"

With "Cells(Rows.Count, 1).End(xlup).Offset(1,0).Select"


If subArea = "SubTxn" Then
Sheets("Vol").Activate
subArea.EntireRow.Select
Selection.Copy
Sheets("CannotFind").Activate
Cells(Rows.Count, 1).End(xlup).Offset(1,0).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial
Selection.Offset(1, 0).Select

ElseIf subArea <> "SubTxn" Then
Sheets("Vol").Activate
subArea.Select
Selection.Resize(Selection.Rows.Count + 3, Selection.Columns.Count + 0).Select
Selection.EntireRow.Select
Selection.Copy
Sheets("CannotFind").Activate
Cells(Rows.Count, 1).End(xlup).Offset(1,0).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial
Selection.Offset(4, 0).Select

End If

[Simon Lloyd]

You also need to look at getting rid of all those selects as they do nothing except slow excel down, here's a sample:
Instead of thisSheets("Vol").Activate
subarea.Select
Selection.Resize(Selection.Rows.Count + 3, Selection.Columns.Count + 0).Select
Selection.EntireRow.Select
Selection.Copy
Sheets("CannotFind").Activate
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial
Selection.Offset(4, 0).Selecttry thisSheets("Vol").Range(subarea.Address & ":" & subarea.Offset(3, 0).Address).EntireRow.Copy _
Destination:=Sheets("CannotFind").Range("A" & Rows.Count, 1).End(xlUp).Offset(1, 0)

[p45cal]

You have based which row to copy things to on column A. If there is not ALWAYS something in column A in what you're copying, subsequent copying is likely to overwrite data in other columns.

[TheAnswer]

You have based which row to copy things to on column A. If there is not ALWAYS something in column A in what you're copying, subsequent copying is likely to overwrite data in other columns.

Yup, it overwrites. I have been wondering why. Is there a way to change to "refering to column D"? Just by changing the range to "D65536"?

And thanks everyone for your replies. I tried out all the codings but they cant seem to work:( The problem is still there.

[p45cal]

Yup, it overwrites. I have been wondering why. Is there a way to change to "refering to column D"? Just by changing the range to "D65536"?Yes.

Or with others' code:

Cells(Rows.Count, 4).End(xlup).Offset(1,0).Select