Transpose skipping columns.

[JapanDave]

I have quite a large worksheet where I need to copy peoples names that are listed on sheet1 going down the page. I am trying to copy these names into a layout on sheet2 that will need to have columns skipped when pasting. The amount of columns to skip is uniform for 4 names, between the 4th and 5th name an extra column appears and will be needed to taken into consideration. This same pattern continues on for up to 300+ names.

For the time being I have posted a small sample file.

[MarvinP]

Hey JapanDave....

Don't do it this way!!!
You have merged columns on sheet 2 and a very ugly pattern to try to paste/transpose to.

Does it have to look like your attached? Can't it be something else?

Would you allow VBA?

[JapanDave]

Hey JapanDave....

Don't do it this way!!!
You have merged columns on sheet 2 and a very ugly pattern to try to paste/transpose to.

Does it have to look like your attached? Can't it be something else?

Would you allow VBA?

Hey, Marvin,

I know exactly what you mean, I hate merged cells as much as you do. The only other way I could possibly do this is by adding a text box. But that just complicates a macro for me even further me.



Dave

[JapanDave]

Jerry, for some reason your code does not copy the last name in the column. Also, I should have included this in the sample file, but that same pattern continues on sheet two for up to 300 names, I just did not include the layout for 300 names.

Just in case my explanation is bad I have included what I am trying to say. Sorry for creating extra work.

[JBeaucaire]

Something like this:

Option Explicit

Sub ParseNames()
Dim ws2 As Worksheet
Dim NmRNG As Range, cel As Long, n As Long

Set ws2 = Sheets("Sheet2")
Set NmRNG = Sheets("Sheet1").Range("C4:C" & Rows.Count).SpecialCells(xlConstants)
n = 1
cel = 2

Do While n < NmRNG.Cells.Count
    ws2.Cells(4, cel).Value = NmRNG.Cells(n).Value
    n = n + 1
    cel = cel + 9
    If cel = 38 Then cel = cel + 1
Loop

End Sub

Would you allow VBA?

Programming forum implies the OP expects the suggestions be VBA.

[MarvinP]

Hey Jerry,

Copy and Transpose were mentioned in the thread. I guess I'm nearing bedtime and didn't see the Programming section.
Still - I hate merged cells in his sheet 2 and thenk Japan Dave can do better.

[JBeaucaire]

Yep, merged cells I strictly save for "final reports", which appears to be what JapanDave might be constructing here.

[JBeaucaire]

My apologies on skipping the last name, forgot the <= in there.

Option Explicit

Sub ParseNames()
Dim ws2 As Worksheet
Dim NmRNG As Range, cel As Long, n As Long

Set ws2 = Sheets("Sheet2")
Set NmRNG = Sheets("Sheet1").Range("C4:C" & Rows.Count).SpecialCells(xlConstants)
n = 1
cel = 2

Do While n <= NmRNG.Cells.Count
    ws2.Cells(4, cel).Value = NmRNG.Cells(n).Value
    n = n + 1
    cel = cel + 9
    If IsNumeric(ws2.Cells(8, cel)) Then cel = cel + 1
Loop

End Sub

[JapanDave]

Thanks Jerry , that did the trick.