Combine Multiple Worksheets Into 1 New Sheet

[dijee]

Hi guys,
I'm new in here.
I have something to ask.
I want to combine multiple worksheets into 1 new sheet, and im trying to do this with VB.
I found some code after searching in this forum.
But then all the code that i found its for column heading style.
What i need is the heading in row style.

Maybe with the file that i attached will help to explain what i mean.

Your help is appreciated.:help

[mohanvijay]

Try this

Dim WS_Con As Worksheet, WS_Sub As Worksheet
Dim i As Byte, k As Byte
Dim L_Col As Integer
Dim j As Long
k = 0
j = 2
Set WS_Con = ActiveWorkbook.Worksheets.Add(ActiveWorkbook.Worksheets(1))
WS_Con.Range("A1:C1") = Array("Name", "Gender", "Age")
Application.ScreenUpdating = False
For Each WS_Sub In ActiveWorkbook.Worksheets
With WS_Sub
If .Name <> WS_Con.Name Then
L_Col = .Cells(1, Columns.Count).End(xlToLeft).Column - 1
If L_Col > 0 Then
For i = 1 To 3
k = k + 1
.Cells(i, 2).Resize(, L_Col).Copy
WS_Con.Cells(j, k).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next i
j = j + L_Col
k = 0
End If
End If
End With
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
Set WS_Sub = Nothing
Set WS_Con = Nothing

[dijee]

hai mohanvijay :hi:;

thanks its works,
but could help me to make the result in the same structure with the example?
I mean the data from left to right.

Sorry for the troublesome.. :friends:

Your help is appreciated.:help