Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am trying to take information from one sheet to another in the same workbook.

However, I would like the positive cells to be imported to the sub sequential sheets with the name of their column.

For example:

           |  Example1 | Example2 | Example3
List One   |     x     |          |   x
List Two   |     x     |    x     |
List Three |     x     |          |

So I would like

  • in Sheet2 - List One

    • Example1
    • Example3

  • In Sheet3 - List Two

    • Example1
    • Example2

  • In Sheet4 - List Three

    • Example1

Hope that makes some kind of sense to someone! Sorry for the terrible coding, rusty rusty rusty!

share|improve this question

migrated from Jul 8 '12 at 23:32

This question came from our site for professional and enthusiast programmers.

I still cannot understand what you are looking for. The values Example1..Example3 are listed in the first sheet and you are looking for a macro or vba code to dispatch the values in other sheets? – Seki Jul 5 '12 at 14:28
perhaps I misunderstood, your tags say excel-formula, but you also say coding is rusty, so I assumed you wanted a vba solution – datatoo Jul 5 '12 at 17:03

I am assuming your titles are in row 1, and data starts in row 2

In the 1st column (row doesn't matter here) of your other sheets, enter this formula =OFFSET(Sheet1!$A$1,0,SMALL(IF(2:2="X",COLUMN(2:2)),COLUMN())-1) as an array formula with Ctrl+Shift+Enter

copy this across the row, and it will list the column names. The end is reached when the formula starts giving #NUM! errors.

For sheet3, change the 2:2 to 3:3 and so on for subsequent sheets

share|improve this answer

This allows you to have a longer number of rows and columns and assumes anything rather than just "x" You may be sure it is always "x" and change this pretty easily. Also I made this name sheets according to their list name. If adding sheets will not occur you can remove that part.

Sub columnsToListSheets()
LastCol = Sheets("Sheet1").UsedRange.Columns.Count
For rowNumber = 2 To 4
i = 1
    ListName = Sheets("Sheet1").Cells(rowNumber, 1)
     NewSheet = ActiveSheet.Name
      Worksheets(NewSheet).Cells(1, 1) = ListName
      'You may want to name the worksheet after the list
      Worksheets(NewSheet).Name = ListName

For colNumber = 2 To LastCol
                                                        '"x" may be good enough to test for
    If Worksheets("Sheet1").Cells(rowNumber, colNumber) <> "" Then
        i = i + 1
        'Worksheets(NewSheet).Cells(i, 1) = Worksheets("Sheet1").Cells(1, colNumber)
        Worksheets(ListName).Cells(i, 1) = Worksheets("Sheet1").Cells(1, colNumber)
    End If

Next colNumber
Next rowNumber
End Sub
share|improve this answer

You must log in to answer this question.