VBA loop to restructure data line by line?

[alanbriggs]

Sorry, the title doesn't help explain anything!
So I am trying to create a .CSV file to import into another system. The data in that CSV needs to come from an un-structured excel spreadsheet.
The source is a sheet with multiple sections containing names in column A, and values in the neighbouring columns. The amount of data is different for each section.
I need to summarise the data so I get a new sheet ready to use as my CSV. It needs to contain a new line with the name, a delimiter (, and the value in the first relevant column that's populated. When I say relevant column, it's the same each time, I want to ignore the data in columns before it. Then the next line is the name again, and the value in the next column. Then when it gets to the last populated column for that row (that persons name), it needs to loop to the next row and do the same.

As each section is different, some with lots of columns of data, I was thinking just physically select the cells I need to work with, and run a macro on the selection, then run it as many times as I want.

Source data, the RED data is what I need...

Destination output would be...

Brian Reynolds;ZBR_EMPLOYEE_BB_RW
Brian Reynolds;ZBR_INVENTORY_MANAGER_BB_RW
Brian Reynolds;ZBR_MANAGER_PROCUREMENT_BB_RO
Bruce Rogers;ZBR_EMPLOYEE_BB_RW
Bruce Rogers;ZBR_INVENTORY_MANAGER_BB_RW
Bruce Rogers;ZBR_MANAGER_PROCUREMENT_BB_RO
Joe Bloggs;ZBR_EMPLOYEE_BB_RW
Joe Bloggs;ZBR_INVENTORY_MANAGER_BB_RW
Joe Bloggs;ZBR_MANAGER_PROCUREMENT_BB_RO
Ryan Farquar;ZBR_EMPLOYEE_BB_RW
Ryan Farquar;ZBR_INVENTORY_MANAGER_BB_RW
Ryan Farquar;ZBR_MANAGER_PROCUREMENT_BB_RO

Sorry, this is blatantly asking for help with no starting point, but I'm in a rush and I'm brushing up on skills I left behind many years ago here. Really appreciate any help I can get.
Thanks in advance, Alan

 

[alanbriggs]

Slept on it, this is the starting point, I think I may just run with this for now...

Sub alan()
Dim rng, cell As Range, rowcnt, refrow As Integer
Set rng = Selection
rowcnt = Sheets("sheet2").UsedRange.Rows.Count
MsgBox (rowcnt)
For Each cell In rng
refrow = cell.Row
Sheets("Sheet2").Range("A" & rowcnt + 1).Value = Sheets("sheet1").Range("A" & refrow).Value & ";" & cell.Value
rowcnt = rowcnt + 1
Next cell
End Sub

 

[Marc L]

I need to summarise the data so I get a new sheet ready to use as my CSV.

No need to use any helper worksheet so from the original worksheet a VBA demonstration to export some data to a text file for starters :​

Sub Demo1()
    Dim F%, V, R&, C%
        F = FreeFile
    With [A1].CurrentRegion
        If .Columns.Count < 10 Then Beep: Exit Sub
       .Sort .Cells(1), 1, Header:=1
        V = .Value2
    End With
        Open ThisWorkbook.Path & "\Export .txt" For Output As #F
    For R = 2 To UBound(V)
    For C = 8 To 10
        If V(R, C) > "" Then Print #F, V(R, 1); ";"; V(R, C)
    Next C, R
        Close #F
End Sub