Referencing Tabs in Excel without Manually Input

[kw127533]

I'm trying to create an Excel spreadsheet that will reference the same cell in different tabs, without having to manually select the data within each tab.

I'm creating a row for the data from each tab and would like the spreadsheet to pull information from those tabs. For example, I would like cell A1 on 'Sheet1' to pull in data from the 'Sheet2' tab cell B4, cell A2 on 'Sheet1' should pull in 'Sheet3' B4 and so on. The cell will be the same in each tab (aka B4).

I have a few ideas but no real solution to this problem. Can someone please help??

[kw127533]

Better yet, instead of separate tabs...what about separate files? Each file is based on a template so they all have the same layout. Is there a way to input the file location (in a cell or via a macro) and have the data automatically pull from the pre-determined cell locations in those files? The data from each file would be on a separate row in the master worksheet.

[twinnyfo]

KW,

Conceptually, here is what you should be trying to do:

Post #1:

VBA opens the Excel Spreadsheet
You have a pointer to Cell A1

This Pointer (x) increments by Rows
It serves as a basis for another Pointer indicating the Sheet (x+1)

Designate the Source Sheet (Sheet[x+1])
Update Cell Ax with the value from Sheet[x+1].B4
Move to the next Row/Sheet (x = x + 1)

Some challenges with this: How many sheets do you have? Do you always have the same number of sheets? Do you have a standardized naming convention for these sheets or just standard "Sheetx"?

Post #2:

VBA Opens your Master Workbook (that saves the stuff you want to keep)
VBA looks in your designated directory for .xls[x] files
It opens the first file and looks for the data
It copies the data to the master file
It moves to the next file

Some challenges with this: How many files are there? How do you keep track of which files have been opened already? Do you delete the files afterward? how confident are you that they will all have the same identical format?

Just some things to think about. What you want to do is doable. What experience do you have with VBA automating MS Excel? This is what you will need to use.