Help with using user input data variable in VBA code for creating Pivot Table from Excel query PLEASE HELP :(

[mwebb]

Hello,

I have been trying to figure this out all day and I cant seem to get it. I have VBA code to create pivot tables from the workbook data model. I added data to it already by using the New Query > Workbook > Create Connection / Add to data model. The table I added from the query is "Table2".

I want to have the user rename from "Table 2" to "MMDDYY_W#" (ie "032116 W1") when they make the connection. So I use this:

    Dim inputData As Variant    inputData = InputBox("Enter Workbook Connection:", "Input Box Text")
    If inputData <> "" Then
        MsgBox inputData
    End If

Next, I have a macro to create pivot tables from the data. It currently works, but I want replace "[Table2]" with the inputData variable throughout so that the macro will pull from the correct data source. An example:

With ActiveSheet.PivotTables("FSBH_Regular_Units").CubeFields("Table2].[Staff]")        .Orientation = xlRowField
        .Position = 1
    End With

I have researched as much as I could but I can't seem to find or understand the answer.

Thanks.

 

[mwebb]

mod can delete I created another thread with the question worded more clearly. Sorry.