How to Insert Row based on specific cell

August 4, 2015 at 19:40:16
Specs: Macintosh
I am looking to automatically insert rows based on specific cells in a different workbook and autofill information into the inserted row.

Example:

In Workbook 1

Type
A B C D
1 0 0 0
0 1 0 1
0 0 0 0
0 0 2 0

So looking at the second row that has 1 of Type B and 1 of Type D - I am referencing each row in Workbook 1 to copy the information into Workbook 2. However, I need each column to go into a separate row and then autofill other information referenced from Workbook 1.

Unfortunately, this is my first attempt at any macro. I was thinking of using an if else statement to set the condition and "ActiveCell.EntireRow.Insert Shift x:=xlDown" to insert the row, but I don't know how to properly reference the cells in Workbook 1 or how to specify where the newly inserted row should be placed.

Is there a way to accomplish this in excel?


See More: How to Insert Row based on specific cell

Report •

#1
August 4, 2015 at 20:13:28
I'm confused by what you are trying to do.

Unfortunately, this doesn't make sense to me:

So looking at the second row that has 1 of Type B and 1 of Type D - I am referencing each row in Workbook 1 to copy the information into Workbook 2. However, I need each column to go into a separate row and then autofill other information referenced from Workbook 1.

You mention a 1 under Type B and Type D, but that doesn't tell us what needs to go where.

Please keep in mind that we can't see your workbook from where we're sitting, so you need to provide as much detail as possible so that we can help you.

Perhaps an example of your input data and your output data might help.

Please click on the following line and read the instructions on how to post data in this forum. Thanks!

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


Report •

#2
August 5, 2015 at 06:08:05
Ok. Hopefully this will clear things up -

In Sheet1 Workbook 1 I have a table that looks like this:

               Fume Hoods				
					
	        4'      5' 	6'	8'   Walk in      
Room A     	0	1	0	0	0
Room B	        1	0	0	0	2
Room C	        0	0	0	0	0

In Sheet2 Workbook 1 I have a table that looks like this:

Fume Hood Maximum 	4' FH 	5' FH 	6' FH	 8'FH   Walk in
	                 480   625	775	  980	 1141

Fume Hood Minimum 	4' FH 	5' FH 	6' FH	 8'F    Walk in
	                 150	150	150	  150	 1141

Where the 4', 5', 6', 8' and Walk in are different types of fume hoods. I have no problem when there is only 1 type of fume hood in a room. However, if there is more than one type, I need to create a new row to make a table that looks like this in Sheet1 Workbook2:

	                  FUME HOOD				
	        QUANTITY	HOOD SIZE	MAX. CFM EACH	MIN. CFM EACH	
Room A	      1                       5	                625	     150	
Room B	      1                       4	                480	     150	
Room B	      1	                  Walk in	        1141	     1141	
Room C	      -                     -	                 -             -	

Does this help clarify what I'm looking to do?

Thanks

message edited by jbluejeans79


Report •

#3
August 5, 2015 at 07:59:59
re: "Does this help clarify what I'm looking to do?"

Yes, to a certain extent, but there is still something confusing, at least to me.

You have clearly described the 2 tables that you currently have in Sheet 1 and Sheet 2, but then you said you "need to create a new row to make a table that looks like this in Sheet1 Workbook2"

I have a couple of questions...

1 - Does the table in Sheet1 Workbook2 already exist? Do you just need to add more rows to that table? If so, how would the VBA code know what rows already exist and what rows need to be added?

2 - In your first table I see a "2" under Room B/Walk In, yet I only see 1 row for Walk In in the Sheet1 Workbook2 table. Is this one of the situations where you need to add another row for the 2nd Walk In or are the 2 entries for Room B on Sheet1 Workbook2 what you want the end result to be?

In other words it's not clear to me if the Sheet1 Workbook2 table is a before or after picture.

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


Report •

Related Solutions

#4
August 5, 2015 at 08:33:55
1. The Sheet1 Workbook2 table is the after picture. It exists but it cannot complete the function that it needs. I have the "Quantity", "Max cfm each", and "min cfm each" columns properly set up to reference what i need it to in Workbook1. Those references are based on what the "Hood Size" column says. When there is multiple values in a row in Sheet1 Workbook1, a new row needs to be added to Sheet1 Workbook2 displaying the other fume hood type and copying the forumlas in cells above to reference the proper information from Workbook1

2. That is a typo. The quantity in the Sheet1 Workbook2 table should be 2.

Does this help?


Report •

Ask Question