Data Connection or VBA?

[kentuckybronco]

The workbook I am working with is called report and I am calling data from a different workbook called "A4". A4 contains countless worksheets, which I would like to call up on a case by case basis. I was able to set up a data connection to draw data from the first worksheet but for my application it would be better if I could collect data from a user defined worksheet. So, if i wanted to select sheet 3 and collect data from it, I would have a prompt that says "select which worksheet you want. All my data is

I can make a VBA code to prompt for the name of a worksheet, but I don't know how I could make that text input dictate to my report workbook which sheet to take from A4. Should I scrap the data connection idea and go with pure VBA? Any suggestions would be greatly appreciated.

 

[kentuckybronco]

Apologies on ending my first paragraph with a sentence fragment...

 

[JackDanIce]

If I understand correctly..

You could have that prompt populate a specified cell (named range/cell?) in your workbook Report and then have formulae reference that cell to collect required data from A4?

Something along the lines of:

Sub ImportMe ()
Dim SelectSheet as String
SelectSheet = Inputbox "Enter name of sheet you want: "
Workbooks("Report").Sheets("Sheet1").Range("A1") = SelectSheet
End Sub

And then the formula in Report would be somthing like:
=VLOOKUP(A2,"'"&A1&"'!A1:J10,5,0)

 

[kentuckybronco]

That would be a start, but could I get it to populate every cell in my Report workbook? I, essentially, want to quickly copy paste from a specific worksheet without having to open the file (it's quite large). When I run the VBA, I'd like it to ask me what sheet I want to access from A4. After I type in the sheet name, it would copy/paste from that A4 worksheet into the Report worksheet.

This way, I can mess with a pivottable in the report workbook to create reports and filter, while at the same time being able to add or edit data in my A4 workbook which is full of data validation dropdowns and checkboxes.