Pulling large amounts of data from varying tabs

**bjvick** · 12-10-2010, 11:12 AM

Alright I'm thinking this should be relatively simple. I have a file that has 11 tabs. Tab1 is the compiled data from all the other tabs and is 200+rows long. The first column of Tab1 pulls data from Tab2 and is completely set up. I want the 2nd column to pull from Tab3, 3rd from Tab4, etc, etc. The tabs are set up the exact same way and the formulas to pull the data from the other tabs into tab 1 are the same except that they pull from different tabs. I want to copy the formulas from column 1 into the next 9 columns and change only the tab they pull from (the cell references are the exact same, the tab they pull from is the only diffference). I'm trying to avoid going through 200+ formulas for each column and changing and only changing the tab from which the datas pulled. Is there a "Next tab" command or something? Please help.

**watersev** · 12-10-2010, 11:44 AM

your sample workbook will help

**bjvick** · 12-10-2010, 11:58 AM

Alright heres a rough sample. The original document is confidential and much much longer. I have the correct cell references in column 1 and want the next columns to pull the same info but from different tabs, without having to edit every single formula. Hopefully this is clear enough.

**DarkF1ame** · 12-10-2010, 01:52 PM

Ok, I don't really have the time to come up with an elegant solution, but here's some rough code assuming you know how to set things up. I set it up to activate when switched to the first tab. You can augment the tab names and cell ranges to fit your needs. It'll be a little front heavy on time but it should do the trick.

Private Sub Worksheet_Activate()

idx = 2
Worksheets("Tab1").Cells(idx, 1) = Worksheets("Tab2").Cells(5, 1)
idx = idx + 1
Worksheets("Tab1").Cells(idx, 1) = Worksheets("Tab2").Cells(11, 1)
idx = idx + 1
Worksheets("Tab1").Cells(idx, 1) = Worksheets("Tab2").Cells(4, 4)
idx = idx + 1
Worksheets("Tab1").Cells(idx, 1) = Worksheets("Tab2").Cells(18, 1)
idx = idx + 1
Worksheets("Tab1").Cells(idx, 1) = Worksheets("Tab2").Cells(18, 6)

idx = 2
Worksheets("Tab1").Cells(idx, 2) = Worksheets("Tab3").Cells(5, 1)
idx = idx + 1
Worksheets("Tab1").Cells(idx, 2) = Worksheets("Tab3").Cells(11, 1)
idx = idx + 1
Worksheets("Tab1").Cells(idx, 2) = Worksheets("Tab3").Cells(4, 4)
idx = idx + 1
Worksheets("Tab1").Cells(idx, 2) = Worksheets("Tab3").Cells(18, 1)
idx = idx + 1
Worksheets("Tab1").Cells(idx, 2) = Worksheets("Tab3").Cells(18, 6)

idx = 2
Worksheets("Tab1").Cells(idx, 3) = Worksheets("Tab3").Cells(5, 1)
idx = idx + 1
Worksheets("Tab1").Cells(idx, 3) = Worksheets("Tab3").Cells(11, 1)
idx = idx + 1
Worksheets("Tab1").Cells(idx, 3) = Worksheets("Tab3").Cells(4, 4)
idx = idx + 1
Worksheets("Tab1").Cells(idx, 3) = Worksheets("Tab3").Cells(18, 1)
idx = idx + 1
Worksheets("Tab1").Cells(idx, 3) = Worksheets("Tab3").Cells(18, 6)

End Sub

etc....

**watersev** · 12-10-2010, 03:23 PM

without macro, see attachment

**Xrull** · 12-10-2010, 11:28 PM

bjvick,
I'm a battering VBA student (for many years).
You can change the sheet names to suit your situation, but if you want to retain the sheet1, sheet2, etc you the other code.
This is by no means the most elegant solution. A guru could improve on it, and use an array Ubound, Lbound or some other techniques to make it much faster instead of the worksheet function choose.

Please Login or Register  to view this content.

If you are using sheet1, sheet2, sheet3

Please Login or Register  to view this content.

**bjvick** · 12-14-2010, 05:24 PM

Hey thanks for the help, I ended up just copying all the column 1 info into column 2 and doing a "Find and Replace" to switch the formula to collect the data from tab1 to tab2. I was unaware that you could have Excel replace information in the formulas for you.