+ Reply to Thread
Results 1 to 14 of 14

Copy/Paste code running very slow

  1. 04-04-2009, 12:02 AM #1
    Zimbo
    Zimbo is offline
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Thumbs up Copy/Paste code running very slow

    Hi All,

    Could someone please help me speed the below code up.
    The code opens a varying workbook with the same format as the source workbook and copies specific columns into the source workbook.
    It is currently taking about 20seconds to run.
    The worksheets are protected and have merged cells hence the call TestMe line.

    All help is appreciated

    Please Login or Register  to view this content.
    Cheers
    Last edited by Zimbo; 04-06-2009 at 11:49 PM.
    Reply With QuoteRegister To Reply
  2. 04-04-2009, 02:19 AM #2
    mudraker
    mudraker is offline
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983

    Re: Copy/Paste code running very slow

    Activating & selecting books, sheets, columns, ranges will make a macro run as will copying & pasting

    The following code shows how to copy values to another sheet without Activating, Selecting, Copying & Pasting

    Where possible I have inserted the equivalant command under the command you used & disabled your commands

    I have added several variables & changed the type of myFilename variable
    Variant variables are the slowestr type of variable

    As I could not test the modified code I can not be certain I modified it 100% correctly.

    Test on a backup coppy of your workbooks

    Please Login or Register  to view this content.
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.
    Reply With QuoteRegister To Reply
  3. 04-04-2009, 03:09 AM #3
    Zimbo
    Zimbo is offline
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Unhappy Re: Copy/Paste code running very slow

    Hi Mudraker,

    Thanks for your code modifications, I can see what you have done and it makes sense.

    The code however is crashing Excel.

    Any suggestions on what I need to change.

    Cheers
    Reply With QuoteRegister To Reply
  4. 04-04-2009, 03:54 AM #4
    Zimbo
    Zimbo is offline
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Red face Re: Copy/Paste code running very slow

    Hi Mudraker,

    The code was crashing Excel because I had placed it within the sheet instead of a module.
    I am getting a runtime error:9 at the following point in the code.
    Please Login or Register  to view this content.
    Cheers
    Reply With QuoteRegister To Reply
  5. 04-04-2009, 12:53 PM #5
    broro183
    broro183 is offline
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Copy/Paste code running very slow

    The "Stuff" file needs to be open before the macro is started, is it open when you run hte macro?

    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...
    Reply With QuoteRegister To Reply
  6. 04-04-2009, 08:45 PM #6
    Zimbo
    Zimbo is offline
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Question Re: Copy/Paste code running very slow

    Hi Broro183,

    The code is being run from the stuff.xls workbook, which is open.

    Cheers
    Reply With QuoteRegister To Reply
  7. 04-05-2009, 12:41 PM #7
    broro183
    broro183 is offline
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Copy/Paste code running very slow

    hi,

    What happens if you change the line to the below code?
    Please Login or Register  to view this content.
    hth
    Rob
    Reply With QuoteRegister To Reply
  8. 04-06-2009, 02:22 AM #8
    Zimbo
    Zimbo is offline
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Re: Copy/Paste code running very slow

    Hi Broro183,

    Thanks for that it cleared the previous runtime error.

    I am now getting a runtime error Code :438 occuring at the following lines in the code.

    Please Login or Register  to view this content.
    Cheers
    Reply With QuoteRegister To Reply
  9. 04-06-2009, 08:22 AM #9
    mudraker
    mudraker is offline
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983

    Re: Copy/Paste code running very slow

    I left an = sign out of the command

    Please Login or Register  to view this content.
    I have just made up a couple of dummy workbooks to test the code on and have identified a few other commands that will cause errors


    Replace the last command in section C
    Please Login or Register  to view this content.
    with
    Please Login or Register  to view this content.
    Replace the last command in section D
    replace
    Please Login or Register  to view this content.
    with
    Please Login or Register  to view this content.
    Replace the last command in section E
    Please Login or Register  to view this content.
    with
    Please Login or Register  to view this content.
    Replace the last command in section F
    Please Login or Register  to view this content.
    with
    Please Login or Register  to view this content.
    Replace the last command in section G
    Please Login or Register  to view this content.
    with
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 04-06-2009, 09:08 AM #10
    Zimbo
    Zimbo is offline
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Re: Copy/Paste code running very slow

    Hi Mudraker,

    Thank you for the code update.
    I think the end of section B needs a change too, I tried to figure it out but i'm having a brain numb day

    Cheers
    Reply With QuoteRegister To Reply
  11. 04-06-2009, 11:53 AM #11
    broro183
    broro183 is offline
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Copy/Paste code running very slow

    hi all,

    Mudraker, I'm sorry about cutting in...


    Try changing
    Please Login or Register  to view this content.
    hth
    Rob
    Reply With QuoteRegister To Reply
  12. 04-06-2009, 11:48 PM #12
    Zimbo
    Zimbo is offline
    Forum Contributor
    Join Date
    02-01-2008
    Location
    Perth, Australia
    MS-Off Ver
    Excel 365
    Posts
    135

    Thumbs up Re: Copy/Paste code running very slow

    Hi Broro,

    I ended up using the below code which worked.
    Please Login or Register  to view this content.
    THANKS to both you and Mudraker,

    This forum and it's contributors are fantastic!
    Reply With QuoteRegister To Reply
  13. 04-07-2009, 02:58 AM #13
    broro183
    broro183 is offline
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Copy/Paste code running very slow

    Thanks for the feedback - I'm pleased we could help :-)

    Rob
    Reply With QuoteRegister To Reply
  14. 04-07-2009, 04:40 AM #14
    mudraker
    mudraker is offline
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983

    Re: Copy/Paste code running very slow

    Zimbo

    Glad to hear your problems sorted out

    broro183
    You will always welcome to butt in - especially when you fix up something I overlooed like using Values instead of Value
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1