+ Reply to Thread
Results 1 to 3 of 3

How can I suppress Open File prompt when copying over formula referencing closed workbooks

  1. #1
    Registered User
    Join Date
    06-24-2013
    Location
    New York
    MS-Off Ver
    Excel 2011
    Posts
    24

    How can I suppress Open File prompt when copying over formula referencing closed workbooks

    I have an Excel file that contains formulas that reference external workbooks on a shared network. Each month, I copy the column of formulas over to the next month's column. Then, to update the file path, I highlight the new column and do a Find-and-Replace, swapping the previous month's name for the current month. This practice works fine. The only thing is ... the "Open File" promptbox appears for each formula where I updated the file path (i.e. and this can be hundreds instances!). I wind-up having to choose the file from the exact same file path. It is an unnecessary, and annoying extra step to take. What is the workaround that I'm missing? I prefer an easy shortcut, but I am familiar with macros, so if this is the only way, then I can handle it.

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,824

    Re: How can I suppress Open File prompt when copying over formula referencing closed workb

    Not sure if this will help, but does setting Calculation to Manual before making the changes make a difference?

  3. #3
    Registered User
    Join Date
    06-24-2013
    Location
    New York
    MS-Off Ver
    Excel 2011
    Posts
    24

    Re: How can I suppress Open File prompt when copying over formula referencing closed workb

    Thanks, Paul, for your response, but I already have it on manual calculate. Still, whenever the formula in each cell tries to pull the value from an external workbook, the file prompt opens even though I already have the correct file path in the formula. If this only happened once or twice it wouldn't be big deal, but I copy these formulas over for each month, and their must be at least 100 instances each month.

+ 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. Breakdown of code for referencing data in closed workbooks
    By gill948 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-01-2011, 12:29 PM
  2. referencing many closed workbooks
    By freud1 in forum Excel General
    Replies: 6
    Last Post: 11-09-2009, 07:20 AM
  3. [SOLVED] Suppress messages/warnings using Workbooks.Open
    By Guy Fawkes in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-15-2006, 04:00 AM
  4. Referencing cells in closed workbooks
    By sastefan in forum Excel General
    Replies: 1
    Last Post: 03-01-2006, 01:10 PM
  5. Referencing Closed workbooks...Help!
    By chris100 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-25-2006, 12:50 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1