Convert current sheets to columns and convert current columns to sheet names: VBA Excel

'This program works like a the paste transpose tool, except we are trading column headers with worksheets, and worksheets with column names.

Sub ColumntoWorksheet()
Dim colHeader, numSheets, colPaste As Integer
Dim NewWS, shtLastYr, shtCurrent  As Worksheet
Dim ShtName  As String



Set shtLastYr = Sheets(4)
For colHeader = 4 To Sheets(1).UsedRange.Columns.Count
    'create new worksheet with column header as the name
    ShtName = Sheets(1).Cells(1, colHeader).Value
    Set NewWS = Sheets.Add
    NewWS.Name = ShtName
    NewWS.Move After:=Sheets(Sheets.Count)
   
    'Copy template text from 2007 to new sheet columns a, b and c
    shtLastYr.Cells(1, 1).EntireColumn.Copy Destination:=NewWS.Cells(1, 1)
    shtLastYr.Cells(1, 2).EntireColumn.Copy Destination:=NewWS.Cells(1, 2)
    shtLastYr.Cells(1, 3).EntireColumn.Copy Destination:=NewWS.Cells(1, 3)
   
    colPaste = 4 'sets the initial column to paste to as 4, then once loop begins will increment
       
    For numSheets = 1 To 4 'iterates through each year
            Set shtCurrent = Sheets(numSheets)
           
            shtCurrent.Cells(1, colHeader).EntireColumn.Copy Destination:=NewWS.Cells(1, colPaste)
            colPaste = colPaste + 1
           
            shtCurrent.Cells(1, colHeader).Offset(0, 1).EntireColumn.Copy Destination:=NewWS.Cells(1, colPaste)
            colPaste = colPaste + 1
    Next numSheets
                    
    colHeader = colHeader + 1
   
Next colHeader

End Sub
 

Comments

[wjx]

Mbt zapatosunder Cuando el trueno le gustaría hay necesidad de buscar tanto miedo Lonely gustarí¿Dóndeestás Mbt zapatosunder. ahoraCuando se quiere esperar ¿Cómo su cuerpo sin alas Cuandovaaquerer volver a casaEn la piscina de ustedes no hanSiempre se ríen dmítonNo reconocer su propio cabelloNo entiendo cómo loHa Mbt zapatosno estado trabajando como locos parapensar en pensar de la mañana a la nocheMiedo se podría pensarTrabajo como un loco para pensaren pensarPensando en ti locoTengo Quieres a morder los dientes blancos Trabajo como un loco para pensar que cree que pensar en elMbt zapatosamanecer。

[Formal Dresses]

Thank for your time to share us this discuss, i have some opinion on the <a href="http://www.dressaler.com/" title="Formal Dresses">Formal Dresses</a>, I feel so stronl about your article, and learning more on this topic, then it is so good, Do you have time visit us <a href="http://www.dressaler.com/wedding-party-dress/bridesmaid-dresses.html" title="Wedding Bridesmaid Dresses">Wedding Bridesmaid Dresses</a>.I will bookmark your blog and have my kids check up here frequently <a href="http://www.dressaler.com/wedding-party-dress/junior-bridesmaid-dresses.html" title="Junior Bridesmaid Dresses">Junior Bridesmaid Dresses</a>. I’m very certain they will understand lots of new stuff here than anybody else. I do appreciate that youve added relevant and intelligent commentary here though. Thank you!