Need Macro that inserts two rows with data after value changes

**KapitalD** · 06-11-2014, 08:39 PM

Hello All,

I've been searching around and finding some promising code but nothing that I can seem to get to work with my level of knowledge.
I started trying to used Gary B's second post in this thread:

HTML Code:

My adapdted code below:

Please Login or Register  to view this content.

However, I think what I'm trying to do is a bit more complex. In fact, there may be a better way to approach this all together. I've attached my data that I'm working with in case it may help solve.

In Column A, When a new customer# appears I need the following: (if duplicate do nothing)

Starting at Row 6
Two rows inserted (I managed to pull this off)
First row I would like to say TOTAL and have cells F:K autosumd (all in bold text)
The Second Row I want to color the inside of the cells black A:L

I can get the two columns inserted but not sure of the best way to do next step. I considered using autofilter and filtering blanks. Then add ToTAL and sum and Bold and black rows and try to apply to rest of rows with copy paste. But its hard to get that approach to work with different data pulls.

Any help would be greatly appreciated!

Thanks, Derek.

**lexusap** · 06-11-2014, 09:04 PM

Hi,

Please see attached file.

This is the macro I use to calculate total of each customer for your account.

Formula:

Please Login or Register  to view this content.

Cheers
Lex

**KapitalD** · 06-12-2014, 07:46 PM

Awesome Lex that's great work!

Now I just need to figure out how I can bold all the cells on the Sub total rows, and make the next inserted row completely black.

Thanks for that!

**KapitalD** · 06-12-2014, 08:01 PM

I would just record the code for autofiltering the blanks inside the current region, or autofilter cells A3 through L3 but the blank rows throw that out of whack.

I'm thinking I can try to filter the blanks somehow and color them all black.
Filtering for the word "Total" and bolding those rows should be easy enough.

I just thought it would be simpler if the row it inserted was already colored in black, and the data was already in bold. But its actually quite difficult for a noob.

**KapitalD** · 06-12-2014, 08:31 PM

I have included in this post a better file for the raw data. I saved the code above from Lexusap to "This Workbook" so that you can run the code quickly and see how it performs against the raw data. The old file I attached previously needed rows taken out of it anyway before you could run the code that Lexusap provided. This is ready to go.

Any ideas on the formatting??

**lexusap** · 06-12-2014, 09:06 PM

Hi KapitalD

see amended formula. I've added a few extra lines to the original codes.
copy paste your code and try to run it again.

Formula:

Please Login or Register  to view this content.

Thanks
Lex

**KapitalD** · 06-29-2014, 11:03 PM

Thanks Lex!

That works like a charm. I will be using this on the end of month reports this coming Tuesday. Just finishing up the final touches.
I really appreciate your help with that part. I would not have been able to do that without you.

Thanks much, Derek.

Need Macro that inserts two rows with data after value changes

LinkBack

Thread Tools

Rate This Thread

Display

Need Macro that inserts two rows with data after value changes

Re: Need Macro that inserts two rows with data after value changes

Re: Need Macro that inserts two rows with data after value changes

Re: Need Macro that inserts two rows with data after value changes

Re: Need Macro that inserts two rows with data after value changes

Re: Need Macro that inserts two rows with data after value changes

Re: Need Macro that inserts two rows with data after value changes

Thread Information

Users Browsing this Thread

Similar Threads

Macro that inserts rows between cells that do not have the same first 5 characters

[SOLVED] Macro that inserts rows stopped working

Macro that Inserts a row in between rows (with formulas etc)

Create a Looping macro which copies and inserts rows

Bookmarks

Bookmarks

Posting Permissions