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