Insert Row In Worksheet One and do the same in worksheet 2

Hi all,

Spent some time trying to fix this issue to no avail. Hope someone can help!

Basically, I've attached a spreadsheet with 2 worksheets, "Summary" and "Detailed".

I would like to create a macro in "Summary" that enables me to insert a row at any position (e.g. insert row to Project 1 - 'above row 12'). While inserting the row, I would like to copy the formulas represented in cells "G:I" (e.g. G8:G11). Cell H11 and I11 both have vlookup formulas that lookup in "Detailed". However, upon inertion of row in "Summary", need to insert 25 rows in "Summary", while copying the cells G6:G31 within respective area of Summary.

My purpose is to set this spreadsheet up, so if anyone wants to insert a task or project to the summary area, they can do so with the click of a button and does not compromise any of the formulas.

Also note, I have code within the sheet that colours the cels within "Summary" "G:I" based on selections. I would like to ensure this is updated as well based on insertion of rows.

I really hope this makes sense!

This forum has been great!
T

Hi

See if this gets you started.

1) I've put in a defined name ListRng on Summary to cover the list table as the location is likely to change.
2) I've updated the worksheet_calculate code to reference the named range, and be a bit more concise.
3) Added a button and some code to start the insertion process.

Can you check this sofar. I'm not sure I have all the items inserting properly, expecially on the Detailed sheet. Put your cursor on the row that where you want to insert the row (say A8) and see how it goes. Try both the Project and non Project rows. If it doesn't work as expected, come back and detail the steps you took, what it did, and what it should have done.

rylo

Hi Rylo,

Thanks very much for your help.

I placed my cursor on "Summary" A11 and it correctly inserted a row. And it also correctly inserted the number of rows including the pasted cells within "Detailed".

What didn't happen: The inserted row in "Summary" included the dropdown list in G11, however the new if/Vlookup formulas were not included in H11 and I11 respectively. As per the formulas in the other rows (e.g. H10 and I10), the formulas need to look up the new cells that were created in "Detailed" worksheet as per new paste (and same as other formulas).

Also, for some strange reason, the new row inserted within "Summary" did not change colour immediately upon selection of an item within its dropdown list (e.g G11). It did however change colour upon selecting a drop-down from one of the original rows.

Hope this clarifies?
Timarcarze

Hi

OK, another go.

Please Login or Register  to view this content.

That should fix up the formula problems, and for me the cells colored when the drop down was actioned. See how it goes now.

rylo

Hi Rylo,

I used that code and pasted it over the existing CommandButton1_Click() in View Code.

It did seem to paste the IF/Vlookup formulas, however the formulas did not reflect the new Table_array within vlookup formula. The table array should be set as the "Detailed" G159:I183 (e.g. if I inserted row in A10 of "Summary").

It also seemed to have a bug of pasting the range at beginning of "Detailed" (e.g. G2:H26), when you hit button.

Thanks
Tim

Tim

1) The example file you gave had a defined name in the vlookup formula, and there were no defined names in the file for reference. Can you please update your example file to reflect your situation with the defined names. Not sure if that is the best way to go, but I'll review when I get a better example of your actual structure.

2) Why would you be inserting rows in Detailed starting at row 2 if you insert at A10 in Summary? Or is your working data again different from your example file.


rylo

Hi Rylo,

>>1) The example file you gave had a defined name in the vlookup formula, and there were no defined names in the file for reference. Can you please update your example file to reflect your situation with the defined names. Not sure if that is the best way to go, but I'll review when I get a better example of your actual structure.
The reference needs to be the new area that is created as part of the Insert. i.e Order4109022 (H10 Summary) is a reference for cells G32:H57 within "Detailed". Are you able to include this new range within your code so it is inserted within Vlookup formula upon placement of cursor and corresponding insert from Detailed?

2) Why would you be inserting rows in Detailed starting at row 2 if you insert at A10 in Summary? Or is your working data again different from your example file.I didn't insert it. It happened automatically once I ran your new code. I have attached the same file you sent me based on the updated "Button" Code

Hope that clarifies. Please refer to my previous message request. Hopefully you can see what happens when you run the attached code.

Cheers
T

Hi Rylo,

Just looked at it again, and I think the attached file might explain it clearer.

(1) Based on placing cursor in A10, I inserted row. The vlookup formula that automatically displayed (based on your code) was
=IF(G10<>"",VLOOKUP(G10,OrderBBD1,2,FALSE),"")
I have changed this to reflect what is needed:
=IF(G10<>"",VLOOKUP(G10,Detailed!G110:I134,2,FALSE),"")
Are you able to fix the code to reflect this? The same would be needed for I10, being =IF(G10<>"",VLOOKUP(G10,Detailed!G110:I134,3,FALSE),"")

Sorry about the last explanation. Also, in response to (2), it appears to have not done that this time?? So is working fine.

See attached updated spreadsheet.

Thanks
T

Hi

Another question first.

I've just copied the data from the named range that I created (ListRng) across to the inserted rows. If this is OK, then can this be used for the vlookup formulas in column H? That way only 1 formula range has to be determined.


rylo

Hi Rylo,

I thought about that and I don't think it will work, particularly for the vlookup in I10 (=IF(G10<>"",VLOOKUP(G10,Detailed!G110:I134,3,FALSE),"")). Basically, we need Column 3 (comments) to be different for each individual project and its sub task. Therefore can't be taken from generic list ('one size fits all').

Does that make sense?

T

Hi

Yep, but the vlookup formula in column H only brings back the responsibility that relates to the material delivery status from column G and it seems to be standard as per the List Table.

I'll work on the principle that it is OK.

1) Change the range of the defined name ListRng to cover column D. So if it is currently C95:C120, it will change to C95:D120
2) Change the formula in H6 to be =IF(G6<>"",VLOOKUP(G6,ListRng,2,FALSE),"") and copy down to H14.
3) Revised code

Please Login or Register  to view this content.

See how that goes.

rylo

Hi Rylo,

In principle I see how this works for H6, however what about I6, where the formula looks up the Comments area within Detail (as mentioned this could be different for each project, which is why I included the look-up table on this worksheet)?

I'm not sure how to fix for that one? Any thoughts?

Tim

Tim

This line of code

Please Login or Register  to view this content.

included in the revise code above will create a specific formula for to cover the column I vlookup.

Have you run the revised code to see how it goes?


rylo

Rylo,

Just tried it again and the code worked (however I didn't change the ListRng - how do I do that?).

The only think that didn't work, when I inserted 2 rows in a row, the second row did not insert the row properly in "Detailed" (it inserted it in G2?)

Tim

Rylo,

Attached is example of spreadsheet, with the updated code.

Note than A10 and A11 (2 rows in a row), I10 shows the formula with the insert occuring in G2 - this seems to happen when paste 2 rows in a row???

I also noticed that the number of rows inserted within "Detailed" does not line up correctly - refer to "Detailed" G160 - this should be in the above row.

Thanks, T

Ooops, attached now.

Tim

For some reason I had the listrng as 25 rows where it should have been 26. This was throwing out the inserted rows and ranges.

I've tried to correct the base data being used, updated the ListRng defined name, and corrected the code for 26 rows not 25. See the attached for the current version.

Can you work with the business practice that you cannot insert a row on an entry that has a blank cell in column A, or a matching entry in Detailed!column A? This could be programmed to check for the existence of the order number in both sheets. That would solve the problem of trying to work out where to put the new item in Details, and you won't have it defaulting to row 2.

If you want to insert multiple rows, then you just have to action them one at a time.

rylo

Thanks Rylo, really well put together! Light years in front of me!

One more question if I may.... When the user inserts a new row in "Summary", if they fill out the 'text' cells A:F (e.g. such as in A6:F6), is there a way to include a copy and past function within the newly created row within "Detailed" that copies across the data A:F in the top line, if it is entered?

Regards
Tim

Hi Rylo,

I'm having problems copying your code to a different workbook.

Its the same spreadsheet but larger. I'm copying and pasting the code within "view code" of that worksheet, but when creating a button (for 'Insert Row'), the button is not linking up to the code properly (saying it does not recognise the macro)?? Any thoughts??

Thanks
T

Tim

1) On your new sheet, create the button from the control toolbox, then select view code. Copy the code component from the test file and paste it into the newly created sub template. This should go across easily.

2) I was wondering how you were going to handle that. One way would be to have an input form raised with the macro and have the user put the info into that form. The data can then be pasted to both Summary and Detailed as part of the action. How would that go???

rylo

Hi Rylo

1) I can't believe, but after quite a bit of fiddling it has worked for me, thanks!!

2) Yeah, that sounds great, any chance you could help build it into the macro? I'm not sure how to do that?

Regards
Timarcarze

Tim

Attach a copy of the example workbook as things stand, and I'll have a look at building an input form.

rylo

Hi Rylo,

Thanks again. See attached updated version with all your great code.

In addition to the form macro...

I was also thinking of including one more button named "Insert New Project", this would insert a project (such as A5 or A12 or A18, etc). This would have the same function as the "Insert Project Material Order" button (that is, insert at any position within "Summary" and insert in same position within "Detailed"). I've tried to manipulate "Insert Project Material Order" button code, but can't get it right??? Can this be done easily?

I've noticed that the fill colour also does not reach across to Collumn I (e.g. I5) for all of the Company 1 Projects. I tried manually fixing but it mucked up the macro for some reason??

Look forward to your thoughts Rylo!

Cheers
T

Tim

Got caught up with the board lockout yesterday.

Have a look at the attached and see how it goes.

If this is OK, then have a go at copying the process for the project insert. Don't really need to have a form but an input box to get the project name. Insertion things would be the same in terms of positioning and you will have to add the relevant formatting.

rylo

Hey rylo

Wondered what happened to the site yesterday.... lockout - sounds serious.

Thanks very much, that works great!

Only thing, it appears to have lost the colour cell format (upon selection of list e.g. column G in Summary) and I noticed you removed some of the code e.g. the following code was sitting within Summary worksheet:

Private Sub CommandButton1_Click()
Dim DetSH As Worksheet
Set DetSH = Sheets("Detailed")
'determine which row to use as the copy base, and determine the project number
'for use in the Detailed sheet insertion
If Left(ActiveCell.Value, 7) = "Project" Then
proj = ActiveCell.Offset(1, 0).Value
Else
proj = ActiveCell.Value
End If
ActiveCell.EntireRow.Insert shift:=xlDown
If IsEmpty(Cells(6, 1)) Then
Range("A7:I7").Copy Destination:=ActiveCell
Else
Range("A6:I6").Copy Destination:=ActiveCell
End If

ActiveCell.Resize(1, 7).ClearContents
ActiveCell.Offset(0, 6).Resize(1, 3).Interior.ColorIndex = xlNone

With DetSH
Set findit = .Range("A:A").Find(what:=proj)
findit.Resize(26, 1).EntireRow.Insert shift:=xlDown
Range("ListRng").Copy Destination:=findit.Offset(-26, 6)
End With

Cells(ActiveCell.Row, "I").Formula = "=IF(G" & ActiveCell.Row & "<>"""",VLOOKUP(G" & ActiveCell.Row & ",detailed!" & findit.Offset(-26, 6).Resize(26, 3).Address & ",3,FALSE),"""")"
End Sub

Private Sub Worksheet_Calculate()
For Each ce In Range("G4:G" & WorksheetFunction.Max(4, Cells(Rows.Count, "G").End(xlUp).Row))
If Not IsEmpty(ce) Then
Set findit = Range("ListRng").Find(what:=ce.Value)
ce.Resize(1, 3).Interior.ColorIndex = findit.Interior.ColorIndex
Else
ce.Resize(1, 3).Interior.ColorIndex = xlNone
End If
Next ce

End Sub

Thanks Tim

rylo,

sorry ignore last post... I feel proud, just worked it out. I added the following code from previous file that changed the colours of cells based on drop-down selection:

Private Sub Worksheet_Calculate()
For Each ce In Range("G4:G" & WorksheetFunction.Max(4, Cells(Rows.Count, "G").End(xlUp).Row))
If Not IsEmpty(ce) Then
Set findit = Range("ListRng").Find(what:=ce.Value)
ce.Resize(1, 3).Interior.ColorIndex = findit.Interior.ColorIndex
Else
ce.Resize(1, 3).Interior.ColorIndex = xlNone
End If
Next ce

End Sub

Cheers and thanks very much for your support on this one!!!!

Hi rylo,

Just when I thought I was done.... I've tried the following 2 things that I'm still stuck with.

(1) I have included a new button "Insert New Project", however I can't seem to get the code right. When I enter a "Project Name", errors appear within code??? Would you please check for me.

However, if I 'end' the error, it correctly copies New Project within "Summary", however the cells within B:D are not coloured correctly and the border lines of that row need to be full line (opposed to dotted).

It also doesn't copy that new project within the Detailed page???

(2) For some reason, whenever I select from the drop-down of column G, the above projects colour vanishes within cells G:I. Is there a way I can stop this from happening.

See update attached.

Thanks Tim

Tim

Updated your v6. Hopefully this has fixed the formatting issue you mentioned. When I tried actioned the dropdown in column G, it seemed to work OK, except for the situation where you went from a colored item to the blank - it didn't bring the color back to default.

rylo

Thanks Rylo,

However, it didn't copy that same row within the same spot of "detailed" worksheet?

Still can't figure that bit out? (looking forward to my VBA 3-day training course next month)

Thanks again
Tim

Ooops, I gotta look through it properly before posting, sorry it works!

However when I add a "New Material Order" below the "New Project", it copies correctly across to the "detailed" worksheet, however the newly posted "Material Order" has black fill cells (same as new project above it).

Is there anyway to fix up the material order code to stop this from occurring?

Thanks Again Rylo
Tim

Rylo!!!

Thanks, fixed up that copy/paste issue, however 3 things are going wrong that I can't work out??

(1) When you insert a "New Project" then follow it by a "New Material" directly underneath it, it correctly copies it across to "Detailed" worksheet, however Column I seems to fill those pasted cells with the colour above it (e.g. goes all black after a "Project" instead of 3 types of coloured cells with broken line borders)

(2) On the "Summary" worksheet, when you insert a "New Material Order" it deletes the 'black' coloured cells of all projects between G & I (e.g. if insert new material in A18, the cells of G5:I5, G12:I12 go all white, instead of remaining black).

(3) When inserting "new material order" in "Summary" the top border is straight, instead of broken line (like the bottom one).

Look forward to check out the code that fixes this.

Thanks
T

Tim

I've updated the previous attachment and should have fixed items 1 and 2. I increased the defined name ListRng to cover an extra column that contains the formatting.

1) Can you check the formatting in this range and make sure it is exactly what you want (borders, colors etc).

2) I think some of the formatting in Detailed has got messed up with the testing. Can you go through this and correct and save this as a new version before testing any further. On a quick check as an example rows 83 and 110 seem wrong.

3) Hopefully your point 3 will be covered with the above actions.


rylo

Hi Rylo,

See attached update based on 'cleaned up formatting' in "Detailed" and check the ListRng - all good (just include bolded line on 3rd column).

Have tested on this attached version. Issues continue:

(1) Insert "New Project" works fine.

(2) When insert "New Material" below a "New Project", it has the correct formatting (eg. straight line border on top of row, broken line border on bottom of row)
However, it does not paste the "broken line" formatting in Detailed (newly inserted area) between A:F (as per rest of worksheet).

(3) When you insert a material between two other materials or below another material, it continues with a straight border on top. Can this top line be broken border?

This would just about do it I think....
Regards
Tim

Tim

Update the defined range to include all the columns and formatting required, not just the 3 columns.

Then update the code to copy over the entire defined name (I think it is time you started learning what the code does he, he, he!!!). This should fix the formatting issue.

rylo

Haha, fair enough Rylo.

I've worked out how to increase define range and equivalent formatting, so that works now (impressed with myself).

However, every time I insert a new 'Project" or "Material Order" within "Summary" it deletes the colour from G:I. I don't know why this is happening because I've checked against previous files and code seems exactly the same?

Can you please check for me! (e.g. insert project and watch black colour disappear between Summary'G:I) Update attached.

Tim

Hi

Try this code. It was actioning a worksheet_change event that was wiping out the color in columns G:I.

rylo

Please Login or Register  to view this content.

Thanks Rylo, thats awesome!

One more tiny thing, which I can't for the life of me work out.... how do I alter the code for "Insert Material Order" to change the Border top edge (ie. I thought to use .Border(x1EdgeTop).Linestyle=x1Dot but didn't work) for the second line down (ie in between two Material Orders).

E.g. it should have a straight top edge 1 after "new project", but the one after that should include dotted top edge.

Hope that makes sense.

Cheers, T

Oh no Rylo..... just when I thought it was fixed.....

When I selected from the one of the drop downs in Summary'G (e.g. G10), the Projects G:I went blank again.

Do I need to add more code to fix this?

Thanks
T

Tim

try updating the change event to

Please Login or Register  to view this content.

rylo

Excellent Rylo, thanks I see how you did it.

Any chance you know the answer to this one?

Originally Posted by timarcarze

Thanks Rylo, thats awesome!

One more tiny thing, which I can't for the life of me work out.... how do I alter the code for "Insert Material Order" to change the Border top edge (ie. I thought to use .Border(x1EdgeTop).Linestyle=x1Dot but didn't work) for the second line down (ie in between two Material Orders).

E.g. it should have a straight top edge 1 after "new project", but the one after that should include dotted top edge.

Hope that makes sense.

Cheers, T

Tim

Just thing aloud here, but have a look at the formatting of the 2 rows involved and see if there is a border in one that is affecting the other. You may have to delete one of the borders.

Alternatively, if this is the copy of the defined named range, then it should be taking the formatting from this block.

Does this help?

rylo

rylo

Nah its not from the defined range. Its from the two rows used, but they don't have specific Border specifications... not sure how to fix it.

Nevermind.... its all good.

Thanks a lot for your help on this. Really taught me a lot.

Tim

If the issue is visible in the last file you put up, give me a reference and I'll see what I can see.

If it only comes in when running, then give me cell references, steps to perform and where the output has the problem.


rylo

Rylo,

I'll step you through attached file.

(1) place cursor in Summary'A8 and click insert New Project, you'll note this appears with a dotted bottem edge. I'd like this to be straight bottom line.

(2) place cursor in Summary!A9 and click insert New Material Order, this appears with straight line at top/dotted edge on bottom, this is corrent for straight under the "Project" row, however when you then place cursor in Summary!A10 it creates straight line at top - I want that to be dotted.

So:
New Project = straight border at top and bottom
New Material Order = dotted border at top and bottom (except if beneath Project, straight line remains for Project bottom edge)

Cheers
Tim

Tim

This should fix the first item. I'll get to the second when I can.

rylo

Please Login or Register  to view this content.

Tim

Hopefully, here is the other bit.

rylo

Please Login or Register  to view this content.

Thanks rylo!
Works great!
Thanks again for all your time, really helped my excel learning.