+ Reply to Thread
Results 1 to 9 of 9

Macro work in personal workbook, doesn't work in other workbooks

  1. 09-30-2013, 10:23 AM #1
    Centexcel
    Centexcel is offline
    Registered User
    Join Date
    02-20-2013
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2010
    Posts
    52

    Macro work in personal workbook, doesn't work in other workbooks

    Excel Forum,

    I have a piece of code that works in my personal workbook, but when I copy and paste it into the workbook that will use the macro I receive a run time error 1004.
    I believe the error occurs after the new workbook is opened and is an object error.

    If more context is needed, all I am trying to do with this macro is data entry from one sheet to another. I want to keep this macro in the sheet where I enter data. The workbook this macro opens changes each month.


    Please Login or Register  to view this content.

    Thank you for your help. This forum is a great resource.
    Reply With QuoteRegister To Reply
  2. 09-30-2013, 10:27 AM #2
    Izandol
    Izandol is offline
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Macro work in personal workbook, doesn't work in other workbooks

    On which line do you have the error?
    Reply With QuoteRegister To Reply
  3. 09-30-2013, 10:29 AM #3
    Centexcel
    Centexcel is offline
    Registered User
    Join Date
    02-20-2013
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Macro work in personal workbook, doesn't work in other workbooks

    I believe it is on the last line.
    I don't get a debug message, I just receive a run-time error and the macro stops.

    Thank you for your help.
    Reply With QuoteRegister To Reply
  4. 09-30-2013, 10:33 AM #4
    Solus Rankin
    Solus Rankin is offline
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Macro work in personal workbook, doesn't work in other workbooks

    Could you please include the code in its entirety and the error type?
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."
    Reply With QuoteRegister To Reply
  5. 09-30-2013, 10:51 AM #5
    Centexcel
    Centexcel is offline
    Registered User
    Join Date
    02-20-2013
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Macro work in personal workbook, doesn't work in other workbooks

    CopyPasteMacro.xlsm

    Attached is the code in its entirety.
    The macro stops after wbNew opens but before column A is unhidden and multiplied by 1.
    I receive a msg box "run time error 1004. Application defined or object defined error."

    As I mention in my first post, the macro works great in my personal book, but fails when I paste it into another workbook.

    Thank you for your time.
    Reply With QuoteRegister To Reply
  6. 09-30-2013, 11:10 AM #6
    Centexcel
    Centexcel is offline
    Registered User
    Join Date
    02-20-2013
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Macro work in personal workbook, doesn't work in other workbooks

    Any ideas?
    Reply With QuoteRegister To Reply
  7. 09-30-2013, 11:16 AM #7
    Solus Rankin
    Solus Rankin is offline
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Macro work in personal workbook, doesn't work in other workbooks

    I'm not exactly sure of the purpose of the macro, however on these lines here: 
    Please Login or Register  to view this content.
    If all these are supposed to apply to Sheets("12 mo inc det") then they will need a . before each line. Otherwise they are going to try to apply to the active workbook. Adjust everything within you "With block" is going to be difficult. You can't just add . a the beginning of the line. You need it in very specific locations within the code. Any time an object refers to the sheet named in the with block it needs to be tied by to that sheet, such as:
    Please Login or Register  to view this content.
    However in some cases it will look like: 
    Please Login or Register  to view this content.
    Like I said, without knowing the purpose of the code its hard to find every object that needs to be tied to the "parent" sheet. I hope this makes sense.
    Reply With QuoteRegister To Reply
  8. 09-30-2013, 11:22 AM #8
    Centexcel
    Centexcel is offline
    Registered User
    Join Date
    02-20-2013
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Macro work in personal workbook, doesn't work in other workbooks

    Solus Rankin,

    I think I understand.

    With my macro in my parent sheet and with the parent sheet open, I am trying to open a workbook with financial data. Next I am trying to unprotect and reformat column A of the new workbook so I can look up key codes and return the values located in column 15. Next I am trying to paste the lookup values into my parent sheet.

    Thank you for your time. I really appreciate it.
    Reply With QuoteRegister To Reply
  9. 09-30-2013, 11:47 AM #9
    Solus Rankin
    Solus Rankin is offline
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Macro work in personal workbook, doesn't work in other workbooks

    Try this: CopyPasteMacro.xlsm

    I don't get any errors but I can't fully test it without the financial information sheets.
    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)

Similar Threads

  1. [SOLVED] Macro Doesn't Work Through Button, Does Work Through Developer ->Macros Option
    By freybe06 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-27-2013, 11:55 AM
  2. macro to copy cells to a new workbook doesn't work properly
    By dileva in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-12-2013, 10:55 AM
  3. [SOLVED] Event macro runs in wrong workbook/doesn't work properly
    By VBA FTW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-30-2012, 11:28 AM
  4. [SOLVED] Macro doesn't work when workbook is renamed
    By tandi in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-23-2012, 01:13 PM
  5. Macro that should create csvs of all the sheets of a workbook doesn't work! help!
    By scantron in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-15-2012, 12:46 PM

Tags for this Thread

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