Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy & Paste Method (Excel 2000)

    Good Morning,

    I have the following code which works fine when using it if you are copying and pasting within the same spreadsheet, however, I want to copy from one spreadsheet and paste into another. Could someone please tell me what the correct code is?

    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim rngSource As Range
    Dim rngTarget As Range

    Set wshSource = Worksheets("PatientInfo")
    Set wshTarget = Worksheets("My524BackUp!MainInfo")***************t his is where the code crashes****************

    Set rngSource = wshSource.Range("A65536").End(xlUp).Resize(1, 5)

    Set rngTarget = wshTarget.Range("A65536").End(xlUp).Offset(1, 0).Resize(1, 5)

    rngSource.Copy
    rngTarget.PasteSpecial Paste:=xlPasteValues

    Set rngTarget = Nothing
    Set rngSource = Nothing
    Set wshTarget = Nothing
    Set wshSource = Nothing

    Thanks in advance
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy & Paste Method (Excel 2000)

    Additions/modifications in bold

    Dim wbkSource As Workbook
    Dim wbkTarget As Workbook

    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim rngSource As Range
    Dim rngTarget As Range

    Set wbkSource = Workbooks("...") ' fill in the name, or ... = ActiveWorkBook
    Set wbkTarget = Workbooks("...") ' fill in the name, probably My524BackUp.xls
    Set wshSource = wbkSource.Worksheets("PatientInfo")
    Set wshTarget = wbkTarget.Worksheets("MainInfo")

    Set rngSource = wshSource.Range("A65536").End(xlUp).Resize(1, 5)
    Set rngTarget = wshTarget.Range("A65536").End(xlUp).Offset(1, 0).Resize(1, 5)

    rngSource.Copy
    rngTarget.PasteSpecial Paste:=xlPasteValues

    Set rngTarget = Nothing
    Set rngSource = Nothing
    Set wshTarget = Nothing
    Set wshSource = Nothing
    Set wbkTarget = Nothing
    Set wbkSource = Nothing

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy & Paste Method (Excel 2000)

    Change

    Set wshTarget = Worksheets("My524BackUp!MainInfo")***************t his is where the code crashes****************
    to:

    Set wshTarget = Workbooks("My524BackUp").Worksheets("MainInfo") '***************this is where the code does not crash****************
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy & Paste Method (Excel 2000)

    Good Afternoon All,

    I tried both suggestions. With both from opening the xlt normally, they work fine. By that I mean, while Excel is open (while I'm still developing), I go to File, Open, and select "LBP" (I've renamed the xlt file since I've last posted) and all works well. However, if I open it from the template folder as I SHOULD (as the User SHOULD) it crashes.

    Dim wbkSource As Workbook
    Dim wbkTarget As Workbook
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim rngSource As Range
    Dim rngTarget As Range

    Set wbkSource = Workbooks("LBP.xlt") ' fill in the name, or ... = ActiveWorkBook *************it crashes here*****************
    Set wbkTarget = Workbooks("LBPBackUp.xls") ' fill in the name, probably My524BackUp.xls
    Set wshSource = wbkSource.Worksheets("AssessmentFU")
    Set wshTarget = wbkTarget.Worksheets("Assessments")

    Set rngSource = wshSource.Range("A65536").End(xlUp).Resize(1, 17)
    Set rngTarget = wshTarget.Range("A65536").End(xlUp).Offset(1, 0).Resize(1, 17)

    rngSource.Copy
    rngTarget.PasteSpecial Paste:=xlPasteValues

    Range("A3:O3").Select
    Selection.ClearContents

    Set rngTarget = Nothing
    Set rngSource = Nothing
    Set wshTarget = Nothing
    Set wshSource = Nothing
    Set wbkTarget = Nothing
    Set wbkSource = Nothing

    Sheets("Updates").Visible = True
    Sheets("Updates").Select
    Sheets("AssessmentFU").Visible = False
    ufUpdates.Show

    Any suggestions as to why?

    Thanks again in advance.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy & Paste Method (Excel 2000)

    If you create a new workbook from the template, the template itself will not be open, so Workbooks("LBP.xlt") will not be valid. Have you tried

    Set wbkSource = ActiveWorkBook

    instead of

    Set wbkSource = Workbooks("LBP.xlt")

    For this to work, the workbook created from the template should be the active workbook in Excel when you run the macro.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copy & Paste Method (Excel 2000)

    Try this instead:
    Set wbkSource = Workbooks("LBP")

    When opened as it should the workbook has no extension. It is an unsaved XLS file. It is not the XLT file.

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy & Paste Method (Excel 2000)

    Thank you both,

    After tinkering awhile, and not sure what I did, all is working now. Below is the code I used to get it to work.

    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim rngSource As Range
    Dim rngTarget As Range

    Set wshSource = Worksheets("PatientInfo")
    Set wshTarget = Workbooks("LBPBackUp").Worksheets("MainInfo")
    Set rngSource = wshSource.Range("A65536").End(xlUp).Resize(1, 15)

    Set rngTarget = wshTarget.Range("A65536").End(xlUp).Offset(1, 0).Resize(1, 15)

    rngSource.Copy
    rngTarget.PasteSpecial Paste:=xlPasteValues

    Range("B4:B23").Select
    Selection.ClearContents

    Set rngTarget = Nothing
    Set rngSource = Nothing
    Set wshTarget = Nothing
    Set wshSource = Nothing

    Sheets("Updates").Visible = True
    Sheets("Updates").Select
    Sheets("PatientInfo").Visible = False
    ufUpdates.Show

    Thanks again for the suggestions.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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