• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vertical to Horizontal Macro

Akshay Salvi

New Member
Hi Excel Gurus,

Need you help in creating a macro to copy data from a vertical format and updating it in horizontal format. formats uploaded.

I have copied 2 data field from Vertical to Horizontal for example, Manually :(.

Following is wht i am trying to make a macro do, data from Agent name to Actual time ( in Vertical file ) should be pasted in Rows C to M ( in horizontal file), data from fields Emulate to Tag in QMX should be pasted from rows N to AO with their respective start and stop times. Then it should move to next set of data and repete above steps.

I have clsoe to 10 files with data in Vertical format, Which is a painful task to convert manually.

Any help would be appreciated
 

Attachments

  • Horizontal.xlsx
    14.6 KB · Views: 28
  • Vertical.xlsx
    20.2 KB · Views: 21
Hi Akshay, and welcome to the forum.

Please see attached, which contains your data, and a macro which will transfer all the info for you.
 

Attachments

  • Vertical_to_Horizontal.xlsm
    28.1 KB · Views: 86
Hi Akshay, and welcome to the forum.

Please see attached, which contains your data, and a macro which will transfer all the info for you.

Hi Luke thank you for prompt response, the macro works perfectly and does as described. Just wanted to confirm, For the other sheets should i copy the data from individual sheets and paste them in the vertical sheet or is there any other way.

regards
 
If it's not too much work, I would suggest pasting them all into the Vertical sheet (keeping the same spacing). Otherwise, if it's a lot of sheets, we can try to setup a loop in the macro.
 
If it's not too much work, I would suggest pasting them all into the Vertical sheet (keeping the same spacing). Otherwise, if it's a lot of sheets, we can try to setup a loop in the macro.

Hi Luke, I can manage to copy paste the data in the vertical sheet, however i just now noticed that the the spacing is not equal between all the data tables, it ranges between 1-3 rows, will it be possible to loop the macro to skip these rows.
 
We can do that. Change your macro to this. For teaching purposes, I left the old code lines in, but commented them out.
Code:
Sub TransferInfo()
'Dim lastRow As Long
Dim recRow As Long
Dim xCounter As Long
Dim timeCounter As Long
Dim sourceWS As Worksheet
Dim destWS As Worksheet

'New variables
Dim firstAdd As String
Dim fCell As Range

'Change these as needed
Set sourceWS = Worksheets("Vertical")
Set destWS = Worksheets("Horizontal")

Application.ScreenUpdating = False

'Where do we start adding rows to destWS?
recRow = 3

With sourceWS
    'Find end of the vertical sheet
    'lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
   
    'Going to have to find all the cells
    Set fCell = .Range("B:B").Find(what:="POS:", after:=.Range("B1"), lookat:=xlWhole)
    firstAdd = fCell.Address
   
    'Don't need this anymore
    'Step through each schedule change
    'For xCounter = 4 To lastRow Step 31
   
    Do
        xCounter = fCell.Row
        .Range(.Cells(xCounter + 1, "E"), .Cells(xCounter + 11, "E")).Copy
        destWS.Cells(recRow, "C").PasteSpecial Paste:=xlPasteValues, Transpose:=True
        Application.CutCopyMode = False
        'Loop through all the different times
        For timeCounter = 14 To 27
            destWS.Cells(recRow, (timeCounter - 14) * 2 + 14).Resize(1, 2).Value = .Cells(xCounter + timeCounter, "C").Resize(1, 2).Value
        Next timeCounter
        recRow = recRow + 1
       
        'Find the next cell
        Set fCell = .Range("B:B").FindNext(fCell)
    Loop Until fCell.Address = firstAdd 'If we're back to first cell, stop
   
    'Next xCounter
End With
Application.ScreenUpdating = True
       
End Sub
 
Back
Top