none
Source Path from a Cell in the Active Worksheet Vs a named cell that is universal to the entire workbook RRS feed

  • Question

  • I am a power query novice so please provide solutions (if there any) at a level I can understand.   Here is my problem: 

    My workbook will contain a large number of worksheets.  The sheet name will be a stock ticker symbol and  a cell on that worksheet contains a formula that retrieves stock ticker from that cell which I have made a named cell.   All of the power query code is written and it retrieves 5 years of data from the intranet without issue.  I want to be able to just copy this worksheet and rename it ( a different stock symbol) and viola be done.  My problem is the power query on this new worksheet refers to original sheet's named cell or named range.

    Is there anyway to pass a parameter from the Active Sheet such that I don't have to go into the query code on each copied worksheet and change the named cell or hard code the source?   In other words,  I want to use the same power query code on multiple worksheets in the same workbook and be able to retrieve the source from Cell A1 of each worksheet without having to go into the code for that worksheet and editing the code.

    Thanks

    Thursday, December 12, 2019 10:42 AM

Answers

  • Hi

    See suggestion at the end of  URL issue when creating power query with VBA (code will need to be adapted). General idea is:

    • Create a generic query a load it as Connection only
    • When adding/copying a worksheet - with your VBA code - take the above generic query code (.Formula)
    • Replace the appropriate values/strings in .Formula
    • Add a new query - with the adjusted .Formula - to your workbook and load it in the ActiveSheet

    BTW if your ActiveSheet is already named as StockTickerSymbol you want query I don't see a need to have a formula + named range on A1 (ActiveSheet.Name should be enough) - or I missunderstood something...

    If it was me I would have a basic macro that:

    • asks user for the new StockTickerSymbol to query
    • creates a new worksheet and rename it accordingly
    • … does what's explained above

    If this solves your problem please Mark as answer (can help others...) - Thanks

    EDIT: Saw a very similar question so quickly put together a sample. Avail. here as a .zip file
    Thursday, December 12, 2019 11:58 AM