VBA for copying from multiple tabs to one master sheet

[ExcelHelpNeeded99]

There are lot of sheets in my workbook, some of the sheets are for personnel data and each employee has a tab of their own. I just want to copy from those sheets and I want to copy the data from column 3 and put it as a separate column in my Master sheet. The master sheet and all the individual personnel sheets have the same 1st column. I would like to include the sheet name (employee name Last, First) at the top of each column if possible.

- How do I tell the macro to only select certain sheets (Probably better to ignore 5 than select 105?)
- Please advise on the code for this.

Your help is greatly appreciated.

Thanks in advance.


Existing format

Row 1	Blank column	Data
(Row 2)First name		Adam
Last name		Ant
Company		Band Co.
Title		Singer
.....		....

<tbody>
</tbody>

Desired combined "Master" sheet

Row 1	Blank	Ant, Adam	Bloggs, Joe
First name		Adam	Joe
Last Name		Ant	Bloggs

<tbody>
</tbody>

 

[ExcelHelpNeeded99]

Can anyone hep? Thanks

 

[Fluff]

This will loop through all sheets ignoring those in red (change to suit).

Sub CopyShtData()

    Dim Arr As Variant
    Dim Sht As Worksheet
    
    Arr = Array("Details", "Data List", "Exist")
    
    For Each Sht In Worksheets
        If Not UBound(Filter(Arr, Sht.Name, True, vbTextCompare)) >= 0 Then
            MsgBox Sht.Name
        End If
    Next Sht

End Sub

If you need help in copying the data over to the Master sheet, we'll need further info.

 

[ExcelHelpNeeded99]

Thanks thats helpful Fluff. What information do you need for the copying to master sheet? Thanks

 

[Fluff]

Is the master sheet called "Master"?
Do you want to copy the entire col C from the employee sheets starting from row2?
From your description it looks like you wnat Col B on the master to be left blank, is that correct?

 

[ExcelHelpNeeded99]

Thanks Fluff. Yes I want to copy from each non red sheet starting at row 2 and going to row 31 just for the data in column C. The master sheet column A is populated with the same field titles as the other sheets and I would like to leave column B blank starting the values from the 1st sheet in column C and so on.

Ideally and I would be so grateful if this would be possible. I would copy the values from column A of the sheets into the master transposed so that they form the column headers and that each sheets data is copied in in separate rows.

	Column 1	Column 2	Column 3	Column 4	….	Column 29
Row 1	First Name	Last name	Company	Title		etc
Data from 1st non red tab	C2	C3	C4	C5		C31
Data from 2nd non red tab	C2	C3	C4	C5		C31
Loop until end of non red sheets

<colgroup><col><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

I would be extremely grateful for either option.

Thanks

 

[Fluff]

Try this, changing sheets names to suit

Sub CopyShtData()

    Dim Arr As Variant
    Dim Sht As Worksheet
    Dim Rw As Long
    
    Arr = Array("[COLOR=#ff0000]Details[/COLOR]", "[COLOR=#ff0000]Data List[/COLOR]", "[COLOR=#ff0000]Exist[/COLOR]", "[COLOR=#ff0000]Master[/COLOR]")
    Rw = 2
    With Sheets("[COLOR=#ff0000]Master[/COLOR]")
        .Range("A1").Resize(, 30).Value = Application.Transpose(.Range("A2").Resize(30).Value)
        .Range("A2:A31").ClearContents
    End With
    For Each Sht In Worksheets
        If Not UBound(Filter(Arr, Sht.Name, True, vbTextCompare)) >= 0 Then
            Sheets("[COLOR=#ff0000]Master[/COLOR]").Range("A" & Rw).Resize(, 30).Value = Application.Transpose(Sht.Range("C2:C31").Value)
            Rw = Rw + 1
        End If
    Next Sht

End Sub

 

[ExcelHelpNeeded99]

Thanks again Fluff- this is awesome!
It is still copying from the red sheets but there aren't many of those and I can just delete that data in the master. What are the "Details", "Data List" and "Exist" in your vba? I am new to this so would appreciate the explanation if you have time.
Thanks so much!

 

[Fluff]

They are sheet names. You need to change them to match the names of the sheets you don't want to copy.