Simple Copy and Paste Mystery

[DanOfEarth]

Hi guys,

Can anyone say why this quick Copy and paste only pastes one worksheet into the new one, not both.

Notice I'm trying to Copy the cell contents of two works sheets and am trying to combine them both to the end of another worksheet. Altough the columns are the same, I cheated because I actually don't know the code to highlight only the full rows, so I hedged it knowing there'll never be more than 4000 rows.

When I "comment out" one, it'll copy the other. But not both. Otherwise it erases the one and pastes theother. That am I missing.

[vba]Sub CutPaste()
' COPY AND PASTE DATA
Sheets("FB").Range("A2:Q4000").Copy Destination:=Sheets("Leads").Range("A1").End(xlUp).Offset(1, 0)
Sheets("Harris").Range("A2:Q4000").Copy Destination:=Sheets("Leads").Range("A1").End(xlUp).Offset(1, 0)
End Sub[/vba]

[Bob Phillips]

Looks like your are overwriting it

[vba]
Sub CutPaste()
' COPY AND PASTE DATA
Sheets("FB").Range("A2:Q4000").Copy Destination:=Sheets("Leads").Range("A1").End(xlDown).Offset(1, 0)
Sheets("Harris").Range("A2:Q4000").Copy Destination:=Sheets("Leads").Range("A1").End(xlDown).Offset(1, 0)
End Sub
[/vba]

[DanOfEarth]

Well

I know it's over writing it for sure, but I thought that the

".End(xlDown).Offset(1, 0)"

...part would find the end of the row again by default. That's why I'm confused.

[Bob Phillips]

It finds the (new) end row each time, then moves down 1.

[DanOfEarth]

It finds the (new) end row each time, then moves down 1.

LOL. I swear I'm smarter than this....us Texans don't think with an accent...only talk with one.

That's EXACTLY what I thought it did. I figured the first time it pasted on the end row.

Now that it "pasted" the first data...on the second "loop" or line rather it then finds the "new" end line....which would be at the end of the pasted data.

I'm not getting it. And darned if I'm gonna let this get away.

[Bob Phillips]

Now that it "pasted" the first data...on the second "loop" or line rather it then finds the "new" end line....which would be at the end of the pasted data.

Sorry I can't understand your accent ... I don't get what you mean here.

[mdmackillop]

Dan,
Your code says xlUP, not xlDown.

Bob,
Your first line will fail if Leads is empty.

[vba]
Sheets("FB").Range("A2:Q4000").Copy Destination:=Sheets("Leads").Cells(Rows.Count, 1).End(xlUp)(2)
Sheets("Harris").Range("A2:Q4000").Copy Destination:=Sheets("Leads").Cells(Rows.Count, 1).End(xlUp)(2)

[/vba]

[mdmackillop]

BTW, try this if the rows may vary, assuming no blank rows/column exist.
[VBA]
Sheets("FB").Range("A2").CurrentRegion.Copy
[/VBA]

[DanOfEarth]

Thanks,

Ultimately I went with the following code. I really needed to clean up the "copy" part, only selecting the used cells instead of all 4000 rows. But the paste part is somewhat the same:

[vba]Sheets("Harris").Select
Set rng = ActiveSheet.UsedRange
rng.Offset(1, 0).Resize(rng.Rows.Count - 1, _
rng.Columns.Count).Copy
Dim ws As Worksheet
'
Set ws = ActiveWorkbook.Sheets("Leads")
'
On Error Resume Next
ws.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlValues
Application.CutCopyMode = False
Sheets("FB").Select
Set rng = ActiveSheet.UsedRange
rng.Offset(1, 0).Resize(rng.Rows.Count - 1, _
rng.Columns.Count).Copy
'
Set ws = ActiveWorkbook.Sheets("Leads")
'
On Error Resume Next
ws.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlValues
Application.CutCopyMode = False[/vba]

I'm still not grasping the concept of "why" the other one was overwriting itself. <sigh> I'm doomed to be mentally blind.

[mdmackillop]

Your code is
[VBA]
Sheets("Leads").Range("A1").End(xlUp).Offset(1, 0)

[/VBA]
This is always A2

[DanOfEarth]

Your code is
[vba]
Sheets("Leads").Range("A1").End(xlUp).Offset(1, 0)

[/vba]
This is always A2

Got it!!

O.K. Now THAT is what I needed! Those four words made more sense than all of the incredible wisdom imparted so far. Sorry guys...you gotta explain it like I'm a three year old.

(God I luv that list of smileys...I don't care what people say)