Add/Remove rows with formatting Macro

[BluOx]

Hello,

I've been trying to piece a macro together from a couple different sites but haven't been able to find a solution for what I'm looking to do. I have an excel doc to track paint emissions per week as well as keep track yearly and monthly totals. I would like to add rows across all 60 some sheets in the spot above the button location while copying all the formatting in the cell above the new row. Additionally I would also like to add a button that will prompt the user for which row they would like to delete and remove it from all sheets.

I certainly appreciate any help, hopefully this will be the last step and I can be done with this document for a while!

~BluOx

 

[BluOx]

I just tried it on a brand new spreadsheet and it does the same thing on all the cells. I've attached another document link, I manually entered the information in row 7. When I ran the macro the formula's went to nine (on row 8), and as I added more lines it it moved them all up one. So when I added a second row the formula's were 10 (on row 8) and 11 (on row 9), third row: 11 (on 8), 12 (on 9), 13 (10).

https://www.dropbox.com/s/n0bxvksssnzs0lj/Test.xlsm?dl=0

 

[mrmmickle1]

BluOx,

That really seems bizarre. On the Sample Mark-Up I made. I had cells with formulas in the table area("C:I", in I22 ) and outside the table area (in A22). Both Formulas copied correctly. Do you still have actual tables? Or did you get rid of them for the color coded alternating row version we developed? Were there any hidden Rows for any reason?

I'll be happy to take a look at the data this evening and get back to you in the morning.

 

[BluOx]

It doesn't make much sense to me either as the formula's in the tables appear to be copying correctly. I did get rid of the actual tables and they are now just colored to look like a table.

I don't believe there are any hidden rows, and it did the same thing when I did it on a brand new document (the one in the dropbox link). What really perplexes me on that was is that it adds a number for all the formula's as a row is inserted, but I don't know why it would...

 

[mrmmickle1]

BluOx,

I had a chance to look this over. I downloaded your spreadsheet and it did not present any problems for me. So here is a before and after....

Before This is Row 7 on Sheet2 :

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L
7	Test	1	2	3	4	5	6	7	8	9	10	11
Sheet4

Cell Formulas
Range	Formula
A7	=IF(Master!A7 ="", "", Master!A7)
B7	=IF(Master!B7 ="", "", Master!B7)
C7	=IF(Master!C7 ="", "", Master!C7)
D7	=IF(Master!D7 ="", "", Master!D7)
E7	=IF(Master!E7 ="", "", Master!E7)
F7	=IF(Master!F7 ="", "", Master!F7)
G7	=IF(Master!G7 ="", "", Master!G7)
H7	=IF(Master!H7 ="", "", Master!H7)
I7	=IF(Master!I7 ="", "", Master!I7)
J7	=IF(Master!J7 ="", "", Master!J7)
K7	=IF(Master!K7 ="", "", Master!K7)
L7	=IF(Master!L7 ="", "", Master!L7)

Then I ran the Macro and this is what resulted when the Prompt asked the row I typed 8:

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L
7	Test	1	2	3	4	5	6	7	8	9	10	11
8
Sheet4

Cell Formulas
Range	Formula
A7	=IF(Master!A7 ="", "", Master!A7)
A8	=IF(Master!A8 ="", "", Master!A8)
B7	=IF(Master!B7 ="", "", Master!B7)
B8	=IF(Master!B8 ="", "", Master!B8)
C7	=IF(Master!C7 ="", "", Master!C7)
C8	=IF(Master!C8 ="", "", Master!C8)
D7	=IF(Master!D7 ="", "", Master!D7)
D8	=IF(Master!D8 ="", "", Master!D8)
E7	=IF(Master!E7 ="", "", Master!E7)
E8	=IF(Master!E8 ="", "", Master!E8)
F7	=IF(Master!F7 ="", "", Master!F7)
F8	=IF(Master!F8 ="", "", Master!F8)
G7	=IF(Master!G7 ="", "", Master!G7)
G8	=IF(Master!G8 ="", "", Master!G8)
H7	=IF(Master!H7 ="", "", Master!H7)
H8	=IF(Master!H8 ="", "", Master!H8)
I7	=IF(Master!I7 ="", "", Master!I7)
I8	=IF(Master!I8 ="", "", Master!I8)
J7	=IF(Master!J7 ="", "", Master!J7)
J8	=IF(Master!J8 ="", "", Master!J8)
K7	=IF(Master!K7 ="", "", Master!K7)
K8	=IF(Master!K8 ="", "", Master!K8)
L7	=IF(Master!L7 ="", "", Master!L7)
L8	=IF(Master!L8 ="", "", Master!L8)

I'm either not understanding the issue properly or I can't recreate the error. Let me know if this was helpful or not. I would be glad to troubleshoot further.

 

[BluOx]

What yours is doing is exactly what I want it to do. When I run it, it inserts the row on the right line, but the formula skips that row and enters what should be on the next row. Each line after that then does the same thing for all the formula's, not just the new one. So they all stay in succession, just not the right numbers...


The only answer I can come up with is that you're using a newer version of Excel than I am. I'm still stuck on 2007 so maybe the code isn't functioning the same...?

 

[mrmmickle1]

Not sure about the reverse compatibility of the code I wrote. I would post a new thread with the code I provided, a link to the workbook, an example of the issue and also reference this thread with a link. Maybe someone else can narrow down the issue. I hate that it's not working properly . I don't have a copy of Excel 2007 so I can't try to replicate the error on my system.

Hopefully, someone with a wider breadth of Excel knowledge can help get your issue sorted.

 

[BluOx]

I'll give that a shot.

Thank you for all the help! I really appreciate your patience with me given my lack of VBA skills and little to no understanding. I've gotten a lot closer than I would have, and probably more efficiently, had I just tried to continue to piece in the different commands for the things I wanted to do. It was getting ugly to say the least...

 

[mrmmickle1]

Sounds like a plan. Like I said, I hate I couldn't get what you wanted 100%. I still think the behavior with that code is strange. I couldn't get the issue to reoccur.

Good Luck. Thanks for the learning opportunity.