Update Macro/VBA with a Reference to a Cell in Workbook

[tleethegreat]

Hello All,

I have a decent understanding of excel. Notwithstanding, I am still a bit of a novice when it comes to VBA.

Here is my dilemma:
In an attempt to automate a query within one of my workbooks I recorded a simple macro to run/update said query. However, there is an issue... my query was setup in a way that always requires the end user to update the date range within the query. For example, when I want to update my data for the month of August 2015 I need to update the date range to 201508, which equals August 2015 in SQL language. So by default my recorded macro has a reference to a specific date for the query update, in this case 201508. I need to be able to update this date by simply referencing a cell in my workbook (let's say cell A1 on the worksheet tab) which will be the point of reference for my macro's date range. So if someone wants to see this information for September 2015 they can type in 201509 in a cell and run the macro for a quick update. I haven't decide on whether the macro will run when a date range is enter in the cell or if there will be a button. Regardless, any one have any ideas on how I can go about making changes to the below string to make this happen?

Please Login or Register  to view this content.

[JBeaucaire]

So I'd think you replace this snippet:

Please Login or Register  to view this content.

....with something like this:

Please Login or Register  to view this content.

[tleethegreat]

Hey JBeaucaire,

I gave this a shot but I got a run-time error "Object variable or With block variable not set". Any thoughts on how I can fix this issue?

Please Login or Register  to view this content.

[JBeaucaire]

Just a guess that cell A2 doesn't have a List object?

[tleethegreat]

Sorry for the delay but I just figured it out. The error occurred because I built the macro while on the query tab, sheet2. The query list is literally my table on sheet2. The problem was I was executing the macro, after updating the date, in sheet1 which is not where my table is stored. So I made a reference to select sheet2 and the macro works like a charm. Can't thank you enough for the help on this! I should have came here 3 weeks ago instead of trying to do this all on my own. Thanks again!

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.