Solved Excel Macro to Add Rows by Column Heading Containing a Value

October 5, 2016 at 20:13:03
Specs: Windows 10
I have 1 identifier (column a) and 10 associated yes/no questions (column b to column k). I need a macro that inserts a separate row in a new sheet for each "No" recorded in those 10 columns. Rather than inserting the entire row, I only want the identifier (column a) and column heading (b1:k1) containing that value.

See More: Excel Macro to Add Rows by Column Heading Containing a Value

Reply ↓  Report •


✔ Best Answer
October 7, 2016 at 11:13:00
Here is some code that should accomplish your goal.

The code assumes that your source data is in Sheet 1 and that you want the output placed in Sheet 2.

Sub CopyHeaderFromNoCol()
'Determine Last Row With Data in Sheet 1 Column A
  lastRw = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
'Loop through Sheet 1 Column A Rows (Names)
   For rw = 2 To lastRw
'Loop through Sheet 1 Columns B:K (Answers)
     For col = 2 To 11
'If Sheet 1 B:K = "No", copy Name and Column Header to Sheet 2
      If Sheets(1).Cells(rw, col) = "No" Then
       nxtRw = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row + 1
         Sheets(2).Cells(nxtRw, 1) = Sheets(1).Cells(rw, 1)
         Sheets(2).Cells(nxtRw, 2) = Sheets(1).Cells(1, col)
      End If
     Next
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
October 6, 2016 at 05:44:06
It is not clear to me what you are asking for. These questions are not necessarily in any specific order, they are simply a mixture of the various items that don't make sense to me.

You say you want insert "a separate row in a new sheet for each "No"

1 - Does that mean that each "No" gets its own new sheet? 40 No's means 40 new sheets?

Then you say "I only want the identifier (column a) and column heading (b1:k1) containing that value."

2 - Are you saying that you want insert a row and copy some data? (You don't say anything about "copying data" in your OP, you only say that you want to insert rows)

3 - What do you mean by "column heading (b1:k1) containing that value."? Are you saying that you want to copy the column heading for the column that contains the "No"?
If so, should it get placed in the same column as the original or someplace else?

4 - "Insert a new row" typically means to place the new row between some existing rows. Do you want to insert a row or to add a row at the bottom of the new sheets(s).

Perhaps a short example of your existing data and the desired output based on that input might help. If you are going to post example data, please click on the following line and read the instructions on how to post example data in this forum. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
October 6, 2016 at 09:59:28
Hi. Thanks for your response. Hopefully I can clarify.

Essentially, I have a questionnaire that consists of 10 yes/no questions for each person listed in column A. For each "No" recorded for a given person, I need a new a separate row added in a new sheet that reflects the individual's name (column A) and the corresponding question number (columns B through K) where that "No" is recorded (i.e. copying the individual's name and column header containing the "No" value). All rows created will need to be in the same new location (i.e. 40 No's in the same sheet).

When those two values are copied (individual's name and corresponding question), name should be in column A and the corresponding question should be in column B.

The rows generated should not be added or inserted in the existing worksheet, but rather in the new sheet.

Essentially, Tom's row in the existing sheet:

     A    B    C    D   E    F    G    H    I   J   K
1    Tom  No   No   No  Yes  No   Yes  No  Yes  No  Yes
2    Sue  Yes  Yes  No  No   Yes  Yes  No  No   No  No
3    Fred No   No   No  Yes  Yes  Yes  Yes Yes  No  No

Would end up looking like this:

     A    B    
1    Tom  B   
2    Tom  C  
3    Tom  D 
4    Tom  F
5    Tom  H 
6    Tom  J     

message edited by gus757


Reply ↓  Report •

#3
October 6, 2016 at 10:55:27
What do you expect us to with that?

Is that even what your data actually looks like? Where are the "column heading (b1:k1) containing that value" that you mentioned?

Are you planning to just ignore all my other questions?

We can't read minds, so you have to give us enough to work with if you want our help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

Related Solutions

#4
October 6, 2016 at 12:28:01
Yes, this is what my data actually looks like. I think my example data initially posted without my other responses, but I edited my post shortly thereafter to answer all your questions. Please let me know if you still have any outstanding questions. Thank you.

Reply ↓  Report •

#5
October 6, 2016 at 13:06:32
OK, now I see the entire post. You are right, it was not there earlier.

I still have questions:

You said: "column heading (b1:k1) containing that value"

1 - In the example data you posted, B1:K1 contain Tom's answers, not any type of "column heading". Are you saying that you want the column *letter* for each "No" placed in the new sheet or is your example data incorrect? Did you leave out the headings that should be in Row 1 so that Tom's data starts in Row 2?

2 - You showed the output for Tom but not for Sue or Fred. Where does their output go - in a new sheet just for their name or at the end of "Tom's list", i.e. is each person's list to be appended to previous person's list?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#6
October 6, 2016 at 14:47:23
I apologize for the inconvenience.

1. In the example data I posted, the column *letter* for each "No" would be placed in the new sheet; however, in my actual data, there row 1 contains headers from column B to column K as follows:

      A   B    C    D    E   F    G    H   I    J   K
1    Name Q1   Q2   Q3  Q4   Q5   Q6   Q7  Q8   Q9  Q10
2    Tom  No   No   No  Yes  No   Yes  No  Yes  No  Yes
3    Sue  Yes  Yes  No  No   Yes  Yes  No  No   No  No
4    Fred No   No   No  Yes  Yes  Yes  Yes Yes  No  No

and would end up looking like this in the new sheet:

     A    B    
1    Tom  Q1   
2    Tom  Q2  
3    Tom  Q3 
4    Tom  Q5
5    Tom  Q7 
6    Tom  Q9 

2. Yes, the output for Sue and Fred would go at the end of Tom's list (i.e. each person's list would be appended to the previous person's list).

Thank you.


Reply ↓  Report •

#7
October 7, 2016 at 11:13:00
✔ Best Answer
Here is some code that should accomplish your goal.

The code assumes that your source data is in Sheet 1 and that you want the output placed in Sheet 2.

Sub CopyHeaderFromNoCol()
'Determine Last Row With Data in Sheet 1 Column A
  lastRw = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
'Loop through Sheet 1 Column A Rows (Names)
   For rw = 2 To lastRw
'Loop through Sheet 1 Columns B:K (Answers)
     For col = 2 To 11
'If Sheet 1 B:K = "No", copy Name and Column Header to Sheet 2
      If Sheets(1).Cells(rw, col) = "No" Then
       nxtRw = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row + 1
         Sheets(2).Cells(nxtRw, 1) = Sheets(1).Cells(rw, 1)
         Sheets(2).Cells(nxtRw, 2) = Sheets(1).Cells(1, col)
      End If
     Next
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#8
October 11, 2016 at 07:51:48
Thank you so much. That code is exactly what I needed to accomplish my goal.

Reply ↓  Report •


Ask Question