Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Copying a sheet from another workbook (XL-XP)

    Here is my problem. I am in the process of creating a generic marking grid for several courses that are being run. I plan to open up an excel workbook that will ask you which is the source file ( There is a excel download that list students from each course that requires marking). I have done this and it works fine with data being copied from the active sheet of the source file which then pastes to the open spreadsheet.

    The only problem is that it takes a long time for the source file to open, ask me if I want to save it and then close down <img src=/S/yawn.gif border=0 alt=yawn width=15 height=15> . Is there a quicker method that I could use (without linking the files as some of us work from home and do not have access to the servers etc) that would activate the source excel workbook without fully opening it so that I can just copy the contents of the sheet to my main workbook?
    Jerry

  2. #2
    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: Copying a sheet from another workbook (XL-XP)

    If you are not changing the source you can close it without the prompt.
    <pre>Workbooks("filename.xls").Close SaveChanges:=False</pre>


    Also instead of opening the source, copying to the desination and then closing the source, you could write a macro that puts formula links to the source cells in the appropriate places in the destination book, then copy and paste-special values these cells over themselves in the destination book. This would alleviate ANY opening of the source workbook.

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copying a sheet from another workbook (XL-XP)

    Thank you Steve, it was SaveChanges:=False that I had missed out in my code...whoops! It now works a dream, except for:

    One small thing that you maybe able to help me with is that as I am copying a section of the worksheet , when it closes it asks me if I want to keep the clipboard open so that it can be pasted from the source to the main workbook. What is the code to automate this to says Yes without a prompt?
    Jerry

  4. #4
    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: Copying a sheet from another workbook (XL-XP)

    Before closing add this line:

    <pre> Application.CutCopyMode = False
    Workbooks("filename.xls").Close SaveChanges:=False</pre>


    Steve

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

    Re: Copying a sheet from another workbook (XL-XP)

    Try Application.CutCopyMode = False after pasting. This is the VBA equivalent of pressing Esc when there is something on the clipboard.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copying a sheet from another workbook (XL-XP)

    Thank you Steve/Hans
    Jerry

Posting Permissions

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