+ Reply to Thread
Results 1 to 4 of 4

RTD value not saved after running more than one open close macro

  1. #1
    Registered User
    Join Date
    03-25-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    47

    RTD value not saved after running more than one open close macro

    I have a workbook1 where I am using RTD to get the value of an index and that is working fine

    I have a separate workbook2 where I want to save that value as a static value so it will not change after it is saved
    I have 9 separate worksheets in this workbook2 where I save each RTD value
    The VBA macro I am using is in workbook2 is opening workbook1 and it is copying a range of cells which includes this RTD value and is pasting it in workbook2

    Here is the problem I am having
    I made a vba macro for each separate sheet and that is working normally and it is saving the value from workbook1

    So trying to save a few steps I am trying to consolidate all the separate macros into one so I copied and pasted each macro into one so it runs one after the other
    The problem is that the value in the first sheet of workbook2 is correct and than everything after that shows 0 in the rest of the worksheets
    Another oddball thing is that after I run the code that runs it one after the other and I try to run the second one separately now the value is still 0
    So in order to make it work I have to open workbook1 let the RTD value update and than save it and than it works if I run it separately but not if run the exact same code one after the other

    I am running this code to open the workbook Workbooks.Open "Path to workbook1"
    and than this one to close it Workbooks("workbook1.xlsm").Close SaveChanges:=True

    Any ideas why the values are saved when running it separately but not saved when the same code is run one after the other ?

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: RTD value not saved after running more than one open close macro

    It is not possible to guess what's wrong based on a general description. Your question is highly specific to the code you have written. When asking a question like this, at a bare minimum you need to provide all of your code. The best possible way to get help is to attach both workbooks.

  3. #3
    Registered User
    Join Date
    03-25-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: RTD value not saved after running more than one open close macro

    I understand but I don't think the rest of the code is relevant
    I have a small update and I found the problem but I am not sure how to fix it

    The problem is with the RTD
    For some reason after the first book is open the second book is not updating
    I ran the code with F8 and saw that as soon as the second book is opened it already shows 0 so of course that is what it is going to save
    So I made a separate macro just to open each workbook without doing anything and it worked
    So I probably have to use 2 separate macros one to open the workbook and another one to do the saving while they are open and than close them
    Of course it would be nice if I could do it in one macro but I don't think it's possible without having the RTD updated first and it doesn't seem possible while the macro code is open

  4. #4
    Registered User
    Join Date
    03-25-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: RTD value not saved after running more than one open close macro

    So my solution for this is to open all the books with one macro so when the end sub of the macro executes the RTD function updates all the values than I can run the second macro to copy and paste the data into workbook2 and close all the open workbooks
    There is no other way to do it because the RTD will not update while excel is busy and the only time that excel is not busy is when the macro finishes with end sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. I need the VBA message box should open and close automatically while macro is running
    By Prabakaran3003 in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 06-13-2017, 02:03 PM
  2. VBA message box should open and close automatically while macro is running
    By Prabakaran3003 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-08-2017, 08:58 AM
  3. [SOLVED] Issue with Open, copy, close Macro - when trying to close workbook Clipboard error
    By seanpcorbett1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-13-2016, 05:14 PM
  4. [SOLVED] Automatically open, close & saved documents
    By namialus in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-24-2014, 08:48 PM
  5. Look for and Open saved workbook retrieve information into new WB then close them
    By Aeneren in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-27-2012, 10:42 PM
  6. Macro to close down word and re-open a previously saved document
    By snoopy1461 in forum Word Programming / VBA / Macros
    Replies: 1
    Last Post: 01-20-2011, 09:59 AM
  7. Open and Close Excel / Time Running
    By tchristie33 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-08-2007, 09:13 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1