Copy a column from multiple sheets paste transpose as a row

[Mel2016]

Hi, what I am trying to do is copy column B (b2:b:19) from all sheets in the workbook (100) and paste/ transpose its one row per record. Each sheet is named with the employee so each has unique data.

The following code works except it's only copying the last worksheet's data in the file to the master sheet instead of starting with the first sheet. I've looked at it too much so any help is greatly appreciated.

Sub TransposeColtoRow()

Dim ws As Worksheet


Application.ScreenUpdating = False
Sheets("Master").Activate

For Each ws In Worksheets
If ws.Name <> "Master" Then
ws.Range("B2:B19").Copy
ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0)
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False



End If

Next ws




End Sub

 

[Joe4]

Where exactly did you place this code?
Is it placed in a Standard Module, or in a Sheet Module?

 

[Mel2016]

Not sure. I am new to this. I use the macro piece from the developer tab in excel. Looks like it's module2.. How do I tell is its sheet or module?

 

[Joe4]

OK. Module 2 should be fine. Out of curiosity, where is the "Master" sheet in relation to all the other sheets (first, last, etc)?
I am going to try to recreate your situation on my end.

BTW, I assumed that you already confirmed that there is data in this range on the other sheets.

 

[JoeMo]

Not sure. I am new to this. I use the macro piece from the developer tab in excel. Looks like it's module2.. How do I tell is its sheet or module?

Right-click the sheet tab and choose "View Code" - do you see your code in the window that opens?

 

[Joe4]

OK. I think I actually found the problem.
Try this:

Sub TransposeColtoRow()

    Dim ws As Worksheet

    Application.ScreenUpdating = False

    For Each ws In Worksheets
        If ws.Name <> "Master" Then
            ws.Range("B2:B19").Copy
            Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
            Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=True
            Application.CutCopyMode = False
        End If
    Next ws
    
    Application.ScreenUpdating = True

End Sub

 

[Mel2016]

Master is the first sheet followed by all the others and yes, all the other sheets have data in the same column/rows. Thanks

 

[Joe4]

See my last post.

 

[Mel2016]

Just saw it. I looked at the master and there wasn't any code. So tried it there but it's still doing something funky? Most of the data in column A but one row from the last worksheet.

 

[Joe4]

What is on the Master sheet when you first start? Is there headers or existing information?
I tested out the following scenario:
- 1 Master sheet followed by three other sheets

What is did was post the data from Sheet 1 Range B2:B19 in cells A2:R2
Then it posted data from Sheet 2 Range B2:B19 in cells A3:R3
Then it posted data from Sheet 3 Range B2:B19 in cells A4:R4

NOTE: If cell B2 is blank in any sheet, it will not work correctly, because it will encounter the blank when trying to find the last used cell in column A on your Master sheet!