Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello,

    The segment of code below is giving me fits.

    The macro is written to automatically open an existing spreadsheet, copy some cells and paste the data into the spreadsheet where the macro resides.

    When I open the spreadsheet and run the macro, it runs exactly as I need without any issues.

    However, if I run it a second time, the copying and pasting is all messed up. It seems to distribute the copied data across several additional columns, acting almost as a Text to Columns function.

    I have to close out of Excel entirely to get it to run properly again. No issues with the first run, but subsequent runs I have problems.

    [codebox]
    Columns("A").Select
    Selection.Copy
    Application.DisplayAlerts = False
    ActiveWindow.Close


    Columns("A").Select
    ActiveSheet.Paste[/codebox]


    I tried modifications to the code to where it only selects and copies the data that is there:

    [codebox] Range("A11").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.copy
    [/codebox]

    I've also tried copying rows instead of columns.

    And I have also selected in the target sheet just the range of A1.

    I've set the display alerts back to "true" after closing, and I do get an alert about the size not matching. I suspect this might have something to do with it, but so far I've not been able to pinpoint exactly where the problem is.


    The spreadsheets I am copying from are in .xls format and the spreadsheet where I am pasting is a .xlsm spreadsheet.

    I am using Excel 2007.

    Thanks in advance for the advice!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Do you have merged cells in the source sheet or destination sheet? That is a known cause of problems when copying and pasting.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='779031' date='09-Jun-2009 15:53']Do you have merged cells in the source sheet or destination sheet? That is a known cause of problems when copying and pasting.[/quote]

    No, nothing is merged.

    There is more code than what I've pasted, so I guess it's possible that something elsewhere is causing the error. But this portion of the code is up near the top and the only thing before it is the opening of the other spreadsheet and the creation of a new worksheet.

    I have also inserted at the beginning of the macro "Application.CutCopyMode = False" to clear out the clipboard.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I just noticed the sentence "The spreadsheets I am copying from are in .xls format and the spreadsheet where I am pasting is a .xlsm spreadsheet.". This means that the source and destination sheet don't have the same number of rows. What happens if you copy a specific range, e.g.

    Range("A11000").Copy Destination:=ThisWorkbook.ActiveSheet.Range("A1")

    (This instruction combines copy and paste)

  5. #5
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='779034' date='09-Jun-2009 16:13']I just noticed the sentence "The spreadsheets I am copying from are in .xls format and the spreadsheet where I am pasting is a .xlsm spreadsheet.". This means that the source and destination sheet don't have the same number of rows. What happens if you copy a specific range, e.g.

    Range("A11000").Copy Destination:=ThisWorkbook.ActiveSheet.Range("A1")

    (This instruction combines copy and paste)[/quote]
    I messed around with that a bit and I couldn't get that to work. However, I may not have been doing it right. I'll give it a shot and see what happens.

    One question (and more info about what the macro is doing)...the spreadsheet that contains the data is not always named the same...it's based on a selection made. The same selection also provide the information for the new spreadsheet, which is where the data will be pasted. Will that make any difference?

    And also, how does the code know which "ThisWorkbook" to paste to? At this point in the code, the spreadsheet that has the data I want to copy is open and active.

    Edited to add...Nevermind the above questions. It worked and I realize how it knew which workbook. It's the workbook that contains the code. Thanks!

    Thanks for being patient with me. I really do appreciate the assistance.

Posting Permissions

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