Hello all!
Attached is a sample of the sheet I am working on. I am pretty new to VBA and so far my code is working as intended. So far, this is what my code does: Whenever data is entered in any cell within the range K10:S41 excel will run a script which copies that cell information into other sheets (range K10:K41 copies into sheet 2 C9:C40, L10:L41 copies into sheet 3 C9:C40 etc.), next there is a script within sheets 2-9 that has automatically hides or unhides rows based on the value of cells C9:C40. So far, so good.
Now a problem arises when I run out of rows and need to insert rows within my sheets. The way I understand it, I have written the VBA code such that the coded range doesn't change when inserting cells, thus VBA doesn't apply the code to the new cells/rows. I believe I need to change the code to refer to Named Ranges rather than "hard coded" ranges yet I don't know exactly how to do that. Also, since the formatting of sheet one corresponds with the formatting of sheets 2-9 I believe I would need to add a VBA code that would recognize whenever I insert a row on sheet 1 within the range of rows 10:41 the code also inserts corresponding rows within sheets 2-9 rows 9:40 (i.e. if I insert a row on sheet 1 in between rows 10 & 11 the code would automatically insert rows within sheets 2-9 and in between rows 9 &10). I believe this would keep my formatting problems under control.
Recap: I would greatly appreciate it if anyone could help me: first, get my VBA code to allow me to insert rows (the range the code applies to would grow if rows were inserted within that range), and second, write a code that would insert rows as described above.
Thanks!
Bookmarks