Copy a column from multiple sheets paste transpose as a row

Mel2016

New Member
Joined
Jun 5, 2016
Messages
41
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
 
Where exactly did you place this code?
Is it placed in a Standard Module, or in a Sheet Module?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
OK. I think I actually found the problem.
Try this:
Code:
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
 
Last edited:
Upvote 0
Master is the first sheet followed by all the others and yes, all the other sheets have data in the same column/rows. Thanks
 
Upvote 0
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.
 
Upvote 0
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!
 
Last edited:
Upvote 0

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