# Combining columns from multiple worksheets into a master

#### catandhorse1976

##### New Member
Hi,

I have 10 + worksheets all in the same excel file. Within each sheet there are two columns (A and B) of identical length (approx 100 rows long). Between sheets, the contents in column A are identical (they are variable names) but the contents of the column B (data values) are different.

I would like to create a master sheet which has all the 'B' columns side by side and a single version of the identical 'A' column on the extreme left. For example, if I have 10 worksheets, the master sheet will then have an A column followed by 10 columns labelled 'B-K'.

Thank you.

e.

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi,
Welcome to Board.

One way of doing it:
1. Add a blank sheet - name it Master or something.
2. In column A copy paste the names from one of the data sheets.
3. In cells B1 to K1 write the Names of the data sheets.
4. In cell B2, enter the formula =INDIRECT(B\$1 & "!B" & ROW())
5. Copy this formula across to column K and then whole thing down to row 100 or wherever the names end.
Thus:
Excel Workbook
ABCD
1NameSheet1Sheet2Sheet3
2ab1237712
3ab2288332
4ab3338952
5ab4389572
6ab54310192
7ab648107112
8ab753113132
9ab858119152
Master
Excel 2003
Cell Formulas
RangeFormula
B2=INDIRECT(B\$1 & "!B" & ROW())
C2=INDIRECT(C\$1 & "!B" & ROW())
D2=INDIRECT(D\$1 & "!B" & ROW())
Excel Workbook
AB
1NameData
2ab123
3ab228
4ab333
5ab438
6ab543
7ab648
8ab753
9ab858
Sheet1
Excel 2003
Excel Workbook
AB
1NameData
2ab177
3ab283
4ab389
5ab495
6ab5101
7ab6107
8ab7113
9ab8119
10ab9125
Sheet2
Excel 2003

Thank you drsarao,

Unfortunately this doesn't quite work because the names of my worksheets are long and variable (they include non consecutive dates, for example). It would be quicker just to copy and paste each of my columns in the master than to write out the names of the worksheets in B1 to K1 as you suggest.

You are then looking for a macro solution.
Try this:
(Modify if needed. I have made some presumption which may not be correct)
First insert a blank sheet naming it Master.
Then copy the following code into a code module in the same workbook.
Run when master is active sheet.

Code:
``````Sub Consolidate()
If ActiveSheet.Name <> "Master" Then Exit Sub
With Sheets("Sheet1")  'change this name if needed
lr = .Cells(Rows.Count, "A").End(xlUp).Row 'last row in sheet 1 to pickup the names.
.Range("A1:A" & lr).Copy Destination:=ActiveSheet.Range("A1:A" & lr) 'copy names
End With
c = 2 'next available column for data
For Each ws In Worksheets
If Not (ws Is ActiveSheet) Then
ActiveSheet.Cells(1, c) = ws.Name 'write sheet name in header
ws.Range("B1:B" & lr).Copy Destination:=ActiveSheet.Range(Cells(2, c), Cells(lr + 1, c)) 'copy data
c = c + 1 'go to next column
End If
Next ws
End Sub``````

You can add a small UDF that will return the Sheet name from it's number.
then you can retrieve the Sheets names using the column numbers. When you have the Sheet name you can use it within the INDIRECT function.
The formula for the Sheet name would be =SheetName(COLUMN())
the formula for the second Sheet (column B in Master) would be:
INDIRECT("'"&(B1)&"'!B"&ROW())

here is the UDF code
Code:
``````Function SheetName(num As Integer)
SheetName = Worksheets(num).Name
End Function``````

Last edited:
Thank you drsarao and iyyi,

I've solved the problem using drsarao's code.

You are welcome!

Replies
1
Views
345
Replies
2
Views
173
Replies
2
Views
582
Replies
1
Views
311
Replies
0
Views
48

Threads
1,203,534
Messages
6,055,954
Members
444,839
Latest member
laurajames

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back