Macro transposing column to rows in step of 15 rows

B

bluetingle

New Member
Joined
Jan 3, 2018
Messages
3
I have a table with columns A-F and several thousand rows. I want to be able to easily transpose the the first rows (2-16) in column E as headings on a new page.
From the F Column i alse need to transpose the data in row 2-16 to the new page. i need to do this for the entire F column. Every 15 step is a new row on the new page. The data looks like this. The left field is Column E and the one to the right is Column F. I would appreciate any help with this.

[TABLE="width: 862"]
<tbody>[TR]
[TD]Adress[/TD]
[TD]Andelsvägen 13[/TD]
[/TR]
[TR]
[TD]c/o[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Efternamn[/TD]
[TD]Komaki[/TD]
[/TR]
[TR]
[TD]Epost[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Förnamn[/TD]
[TD]Birgitta[/TD]
[/TR]
[TR]
[TD]Har du erfarenhet av röstmottagning?[/TD]
[TD]Ja[/TD]
[/TR]
[TR]
[TD]Kan du arbeta heltid 22 aug - 9 sep?[/TD]
[TD]Ja[/TD]
[/TR]
[TR]
[TD]Mobiltelefonnummer[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Om ja, var och när?[/TD]
[TD]Stockholm innerstad olika valkretsar samt Akalla. Har varit med nästan alla val sedan 1973.[/TD]
[/TR]
[TR]
[TD]Övrigt (andra meriter)[/TD]
[TD]Önskemål Arbetar gärna nära Odenplan eller Centralen , åker med SL från Sollentuna.[/TD]
[/TR]
[TR]
[TD]Personnummer ååmmdd-xxxx[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Postnummer xxx xx[/TD]
[TD]19149[/TD]
[/TR]
[TR]
[TD]Postort[/TD]
[TD]Sollentuna[/TD]
[/TR]
[TR]
[TD]Språkkunskaper utöver svenska och engelska[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Telefon[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Adress[/TD]
[TD]Nyponrosvägen, 5, 5[/TD]
[/TR]
[TR]
[TD]c/o[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Efternamn[/TD]
[TD]Bäckman[/TD]
[/TR]
[TR]
[TD]Epost[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Förnamn[/TD]
[TD]Marianne[/TD]
[/TR]
[TR]
[TD]Har du erfarenhet av röstmottagning?[/TD]
[TD]Ja[/TD]
[/TR]
[TR]
[TD]Kan du arbeta heltid 22 aug - 9 sep?[/TD]
[TD]Ja[/TD]
[/TR]
[TR]
[TD]Mobiltelefonnummer[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Om ja, var och när?[/TD]
[TD]alla val sedan jag var 20 år[/TD]
[/TR]
[TR]
[TD]Övrigt (andra meriter)[/TD]
[TD]alla va förtidsröstning och ordförande nu i sofia 2[/TD]
[/TR]
[TR]
[TD]Personnummer ååmmdd-xxxx[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Postnummer xxx xx[/TD]
[TD]13833[/TD]
[/TR]
[TR]
[TD]Postort[/TD]
[TD]Älta[/TD]
[/TR]
[TR]
[TD]Språkkunskaper utöver svenska och engelska[/TD]
[TD]svenska och engelska[/TD]
[/TR]
[TR]
[TD]Telefon[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Adress[/TD]
[TD]Kungsgatan 51[/TD]
[/TR]
[TR]
[TD]c/o[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Efternamn[/TD]
[TD]Sagindykova[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Re: Urgent help with macro transposing column to rows in step of 15 rows

please ask with a very simple pretend layout say 4 cols 10 rows and your desired output
 
Upvote 0
Re: Urgent help with macro transposing column to rows in step of 15 rows

This is the layout i'd like to achieve:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Adress[/TD]
[TD]c/o[/TD]
[TD]Efternamn[/TD]
[TD]Epost[/TD]
[TD]Förnamn[/TD]
[TD]Har du erfarenhet av röstmottagning?[/TD]
[TD]Kan du arbeta heltid 22 aug - 9 sep?[/TD]
[TD]Mobiltelefonnummer[/TD]
[TD]Om ja, var och när?[/TD]
[TD]Övrigt (andra meriter)[/TD]
[TD]Personnummer ååmmdd-xxxx[/TD]
[TD]Postnummer xxx xx[/TD]
[TD]Postort[/TD]
[TD]Språkkunskaper utöver svenska och engelska[/TD]
[TD]Telefon[/TD]
[/TR]
[TR]
[TD]Andelsvägen 13[/TD]
[TD][/TD]
[TD]Komaki[/TD]
[TD]x@y[/TD]
[TD]Birgitta[/TD]
[TD]Ja[/TD]
[TD]Ja[/TD]
[TD]xxxxx[/TD]
[TD]Stockholm innerstad olika valkretsar samt Akalla. Har varit med nästan alla val sedan 1973.[/TD]
[TD]Önskemål Arbetar gärna nära Odenplan eller Centralen , åker med SL från Sollentuna.[/TD]
[TD]xxxxxx[/TD]
[TD]19149[/TD]
[TD]Sollentuna[/TD]
[TD][/TD]
[TD]xxxxx[/TD]
[/TR]
[TR]
[TD]Nyponrosvägen, 5, 5[/TD]
[TD][/TD]
[TD]Bäckman[/TD]
[TD]y@x[/TD]
[TD]Marianne[/TD]
[TD]Ja[/TD]
[TD]Ja[/TD]
[TD]xxxxx[/TD]
[TD]alla val sedan jag var 20 år[/TD]
[TD]alla va förtidsröstning och ordförande nu i sofia 2[/TD]
[TD]xxxxx[/TD]
[TD]13833[/TD]
[TD]Älta[/TD]
[TD]svenska och engelska[/TD]
[TD]xxxxxx[/TD]
[/TR]
</tbody>[/TABLE]


So far this is what i have come up with but it doesn't really behave the way i want :D

Code: 
Sub SCU()
    Dim Gen As Worksheet
    Dim Ink As Worksheet
    Dim i As Integer
    Dim z As Integer
    Dim x As Integer
    
    'Set Gen = Sheets("Generated")
    Set Ink = Sheets("inkomna")
    
    i = Cells(Rows.Count, "E").End(xlUp).Row
    z = 2: x = 1
    While z <= i
    Ink.Range("F2" & x).Resize(, 15) = _
            WorksheetFunction.Transpose(Range("F" & z).Resize(15))
        z = z + 15: x = x + 1
    Wend
End Sub
 
Upvote 0
Re: Urgent help with macro transposing column to rows in step of 15 rows

How about
Code: 
Sub CopyTrans16()

   Dim Rw As Long
   Dim i As Long
   
   Rw = 2
   With Sheets("Sheet2")
      .Range("A1:O1").Value = Application.Transpose(Range("E2:E16"))
      For i = 2 To Range("F" & Rows.Count).End(xlUp).Row Step 15
         .Range("A" & Rw).Resize(, 15).Value = Application.Transpose(Range("F" & i).Resize(15))
         Rw = Rw + 1
      Next i
   End With
End Sub
The "Data" sheet needs to be the active sheet & this will copy to "Sheet2"
 
Upvote 0
Re: Urgent help with macro transposing column to rows in step of 15 rows

[TABLE="width: 1211"]
<colgroup><col><col><col><col><col><col><col><col><col span="3"><col></colgroup><tbody>[TR]
[TD]Adress[/TD]
[TD]Andelsvägen 13[/TD]
[TD][/TD]
[TD]Adress[/TD]
[TD]c/o[/TD]
[TD]Efternamn[/TD]
[TD]Epost[/TD]
[TD]Förnamn[/TD]
[TD]Har du erfarenhet av röstmottagning?[/TD]
[TD]Kan du arbeta heltid 22 aug - 9 sep?[/TD]
[TD]Mobiltelefonnummer[/TD]
[TD]Om ja, var och när?[/TD]
[/TR]
[TR]
[TD]c/o[/TD]
[TD][/TD]
[TD][/TD]
[TD]Andelsvägen 13[/TD]
[TD][/TD]
[TD]Komaki[/TD]
[TD]x[/TD]
[TD]Birgitta[/TD]
[TD]Ja[/TD]
[TD]Ja[/TD]
[TD]x[/TD]
[TD]Stockholm innerstad olika valkretsar samt Akalla. Har varit med nästan alla val sedan 1973.[/TD]
[/TR]
[TR]
[TD]Efternamn[/TD]
[TD]Komaki[/TD]
[TD][/TD]
[TD]Andelsvägen 14[/TD]
[TD][/TD]
[TD]Komakioo[/TD]
[TD]x[/TD]
[TD]Birgittaxxx[/TD]
[TD]Nein[/TD]
[TD]Ja[/TD]
[TD]x[/TD]
[TD]different text[/TD]
[/TR]
[TR]
[TD]Epost[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Förnamn[/TD]
[TD]Birgitta[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Har du erfarenhet av röstmottagning?[/TD]
[TD]Ja[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kan du arbeta heltid 22 aug - 9 sep?[/TD]
[TD]Ja[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]this macro pulled the data into the table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mobiltelefonnummer[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]just adjust it for actual number of records = rows in a block of data[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Om ja, var och när?[/TD]
[TD]Stockholm innerstad olika valkretsar samt Akalla. Har varit med nästan alla val sedan 1973.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Adress[/TD]
[TD]Andelsvägen 14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Sub Macro4()[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c/o[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Efternamn[/TD]
[TD]Komakioo[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]' Macro4 Macro[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Epost[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]' Macro recorded 27/02/2018 by bob[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Förnamn[/TD]
[TD]Birgittaxxx[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Har du erfarenhet av röstmottagning?[/TD]
[TD]Nein[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kan du arbeta heltid 22 aug - 9 sep?[/TD]
[TD]Ja[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mobiltelefonnummer[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Sum = 0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Om ja, var och när?[/TD]
[TD]different text[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] Count = -8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] For t = 2 To 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] Count = Count + 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] For s = 4 To 12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"] Cells(t, s) = Cells((s - 3) + Sum, 2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Next s[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] Sum = Sum + 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Next t[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]End Sub[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: Urgent help with macro transposing column to rows in step of 15 rows

Thank you Fluff, you solved my problem. Deeply appreciated
 
Upvote 0
Re: Urgent help with macro transposing column to rows in step of 15 rows

Glad to help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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