Combining a single column of data from multiple sheets into one

LillN

New Member
Joined
May 25, 2015
Messages
3
Hi all,

I was hoping to get some advice regarding merging data from different worksheets in Excel 2010.

I currently have a workbook with around ~500 individual sheets, each containing a single column of data, where the sheet title is the name of each column of data (eg. the title of the first sheet is Pt101 and contains data from participant 101). I would like to merge the sheets into a single sheet, with each column separate (ie. Pt1 would be in column A, Pt2 would be in column B etc). Ideally I was also hoping to have the sheet title as the header row as well. I've included a sample of the data, and an example of what I was hoping to get in the end.

Searching on this has pretty much just brought me to the 'consolidate' option, however I don't think this achieves what I was after. I'm also pretty new to VBA.

I was wondering if anyone had any suggestions on how to do this, or even whether it is possible.

Any advice/assistance would be greatly appreciated!


Thank you


Excel 2010
D
18
Pt1
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
What column is the participant data in on each sheet
 
Upvote 0
Assuming your Master sheet is named "Master", try
If not change Master in the code to whatever you MAIN sheet will be !!
Code:
Sub MM1()
Application.ScreenUpdating = False
Dim lc As Integer, lr As Long, ws As Worksheet
lc = Sheets("Master").Cells(1, Columns.Count).End(xlToLeft).Column
For Each ws In Worksheets
    If ws.Name <> "Master" Then
        lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
        ws.Range("A2:A" & lr).Copy Sheets("Master").Cells(2, lc + 1)
        Sheets("Master").Cells(1, lc + 1).Value = ws.Name
        lc = Sheets("Master").Cells(1, Columns.Count).End(xlToLeft).Column
    End If
Next ws
Application.ScreenUpdating = True
End Sub
 
Upvote 0
That worked perfectly, thank you so much Michael!

You've saved me so much time, really appreciate your help!

Lill
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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

Which adblocker are you using?

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
Back
Top