Results 1 to 5 of 5

Macro to refresh connections and add formulas

Threaded View

  1. #1
    Registered User
    Join Date
    06-14-2017
    Location
    London, England
    MS-Off Ver
    Professional Plus 2016
    Posts
    7

    Macro to refresh connections and add formulas

    I have an Excel file with a number of connections which can be refreshed. I am trying to build the following macro to a) refresh all connections throughout the workbook, b) add a value to two specific cells on one worksheet, then c) add two formulas which should be applied as far as the last row of data (once the connections have been refreshed):

    Sub GetData()
    Sheets("AllCallsV3").Cells.Clear
    ThisWorkbook.RefreshAll
    Worksheets("AllCallsV3").Activate
    Range("N1").Value = "Exclude"
    Range("O1").Value = "Include"
    Sub GetData()
    Sheets("AllCallsV3").Cells.Clear
    ThisWorkbook.RefreshAll
    Worksheets("AllCallsV3").Activate
    Range("N1").Value = "Exclude_Stop_Code"
    Range("O1").Value = "Include_DX_Code"
    Range("N3:N" & LastRow).Formula = "=IF(ISNUMBER(MATCH(F3,Exclusions!$A:$A,0)),1,0)"
    Range("O3:O" & LastRow).Formula = "=IF(ISNUMBER(MATCH(I3,Exclusions!$C:$C,0)),1,0)"
    End Sub
    This returns an error as follows:

    ErrorMessage.PNG

    If I choose 'End', the connections continue to refresh and the values are inserted into cells N1 and O1 but the formulas are not added.

    Any ideas? I'm pretty new to VBA so apologies if I've made some very basic errors!
    Last edited by AliGW; 01-02-2018 at 05:24 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Refresh all data connections, save and close
    By izk630 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-27-2016, 10:58 AM
  2. Refresh All vs. Refresh All Data Connections
    By TK92 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-22-2016, 09:14 AM
  3. Refresh data connections with delay via macro
    By Jimmyjazz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-20-2013, 01:57 AM
  4. Refresh Specific Sheets/ Connections
    By hyp3rbola in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-31-2013, 04:50 PM
  5. Safe way to refresh many connections without saving the password?
    By bristly in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-24-2013, 06:29 AM
  6. [SOLVED] Data Tab -- Refresh All data Connections on multiple sheets - Need a macro
    By aetedford in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-15-2013, 06:40 PM
  7. Macro to Refresh data Connections in each Tab of an excel
    By pnandak in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-09-2012, 04:47 AM

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