Thread: Automatically insert row & Transpose

Hello everybody,

I'm new to this forum and I hope somebody can help me with my spreadsheet.

I will pls. need a macro that will automatically insert rows and copy the same information but at the same time insert new columns (Column S to V) to transpose the hours in each day. Pls. separate each person with a straight line after grouping them. (I did 5 names as an example of what I need)

Attach is the spreadsheet for reference.

Appreciate any help. Thanks!

Try this:

Code:

Option Explicit

Sub ReformatData()
'JBeaucaire   3/12/2010
Dim LR As Long, Rw As Long, i As Long
Dim DateArr As Variant
Application.ScreenUpdating = False

LR = Range("A" & Rows.Count).End(xlUp).Row

Columns("S:V").Insert xlShiftToRight
Range("S1:V1").Value = [{"Year","Month","Day","Hours"}]

    For i = 23 To 29
        Range("BA" & i - 22, "BC" & i - 22) = Split(Cells(1, i), "_")
    Next i
    Range("BB:BB").Cut Range("BD1")
    Range("BA:BA").Cut Range("BE1")
    DateArr = Range("BC1:BE7")
    Range("BC1:BE7").ClearContents
    
    For Rw = LR To 2 Step -1
        Rows(Rw).Copy
        Rows(Rw + 1 & ":" & Rw + 6).Insert xlShiftDown
        Range("S" & Rw).Resize(7, 3) = DateArr
        Range("W" & Rw, "AC" & Rw).Copy
        Range("V" & Rw).PasteSpecial xlPasteValues, Transpose:=True
    Next Rw

Columns("W:AC").ClearContents
Columns.AutoFit
Application.ScreenUpdating = True
End Sub

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.