PDA

View Full Version : Simple Copy and Paste Mystery



DanOfEarth
06-01-2010, 05:22 AM
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.

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

Bob Phillips
06-01-2010, 05:26 AM
Looks like your are overwriting it


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

DanOfEarth
06-01-2010, 05:43 AM
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
06-01-2010, 05:44 AM
It finds the (new) end row each time, then moves down 1.

DanOfEarth
06-01-2010, 12:06 PM
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
06-01-2010, 12:26 PM
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
06-01-2010, 12:41 PM
Dan,
Your code says xlUP, not xlDown.

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


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)

mdmackillop
06-01-2010, 12:45 PM
BTW, try this if the rows may vary, assuming no blank rows/column exist.

Sheets("FB").Range("A2").CurrentRegion.Copy

DanOfEarth
06-01-2010, 01:09 PM
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:

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

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

mdmackillop
06-01-2010, 01:12 PM
Your code is

Sheets("Leads").Range("A1").End(xlUp).Offset(1, 0)


This is always A2

DanOfEarth
06-01-2010, 01:50 PM
Your code is

Sheets("Leads").Range("A1").End(xlUp).Offset(1, 0)


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.:bink:

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