Consulting

Results 1 to 11 of 11

Thread: Simple Copy and Paste Mystery

  1. #1

    Simple Copy and Paste Mystery

    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]

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,268
    Location
    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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    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.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,268
    Location
    It finds the (new) end row each time, then moves down 1.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Quote Originally Posted by xld
    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.

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,268
    Location
    Quote Originally Posted by DanOfEarth

    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  8. #8
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    BTW, try this if the rows may vary, assuming no blank rows/column exist.
    [VBA]
    Sheets("FB").Range("A2").CurrentRegion.Copy
    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  9. #9
    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.

  10. #10
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Your code is
    [VBA]
    Sheets("Leads").Range("A1").End(xlUp).Offset(1, 0)

    [/VBA]
    This is always A2
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  11. #11
    Quote Originally Posted by mdmackillop
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •