Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Insert rows
I currently linked worksheet 1 to a hidden and protected worksheet 2. Wksht
1 allows the user to select from a list of categories using a drop-down menu I set-up via "Data Validation". Each row of wksht 1 corresponds to a category. My question is 2 part: 1) Is it possible to have wksht 1 insert a new row automatically everytime a different category is selected from the drop down menu? Basically I'm trying to reduce the amount of rows appearing on the screen when not utilized. Currently, each category is assigned 1 row, while other categories are assigned several rows. Depending on the User, categories with multiple rows are often not applicable and are thus left blank. 2) Wksht 2 is what the User prints via macro button from wksht 1. I'd like for wksht 2 (hidden/protected) to reflect the new rows added to wksht 1. Basically, I'm trying to omit certain blank rows before printing. Does this have to be done via a formula or macro? If macro, can my existing macro (print button) be modified to filter out the blank rows before printing? Here's an example of what my current macro reads: Sub Macro10() ' Macro1 Macro ' Macro recorded 11/14/2004 by Gary Mendoza ' ' Application.EnableCancelKey = x1Disabled Application.ScreenUpdating = False ActiveWorkbook.Unprotect Password:="lindAP" Sheets("WKLY-RPT").Visible = True Sheets("WKLY-RPT").Unprotect Password:="lindAP" Sheets("WKLY-RPT").select ActiveWindow.LargeScroll ToRight:=3 Range("A1:K51").select Range("A1").Activate activesheet.PageSetup.PrintArea = "$A$1:$K$51" ActiveWindow.SelectedSheets.PrintPreview Sheets("WKLY-RPT").Visible = False ActiveWorkbook.Protect Structu=True, Windows:=False, Password:="lindAP" Sheets("EXP RPT").Visible = True Sheets("EXP RPT").select Range("K10").select Application.ScreenUpdating = False Application.EnableCancelKey = x1Interrupt End Sub |
#2
|
|||
|
|||
Question 1: Yes. It's rather simple really. But how does Excel know how
many rows to add ("Currently, each category is assigned 1 row, while other categories are assigned several rows.")? Question 2: Yes, you can add code to the macro to massage the range any way you want before printing. But you will have to furnish more detail about your data layout and what blank rows you want deleted before printing. All of them? HTH Otto "Mr. G." <Mr. wrote in message ... I currently linked worksheet 1 to a hidden and protected worksheet 2. Wksht 1 allows the user to select from a list of categories using a drop-down menu I set-up via "Data Validation". Each row of wksht 1 corresponds to a category. My question is 2 part: 1) Is it possible to have wksht 1 insert a new row automatically everytime a different category is selected from the drop down menu? Basically I'm trying to reduce the amount of rows appearing on the screen when not utilized. Currently, each category is assigned 1 row, while other categories are assigned several rows. Depending on the User, categories with multiple rows are often not applicable and are thus left blank. 2) Wksht 2 is what the User prints via macro button from wksht 1. I'd like for wksht 2 (hidden/protected) to reflect the new rows added to wksht 1. Basically, I'm trying to omit certain blank rows before printing. Does this have to be done via a formula or macro? If macro, can my existing macro (print button) be modified to filter out the blank rows before printing? Here's an example of what my current macro reads: Sub Macro10() ' Macro1 Macro ' Macro recorded 11/14/2004 by Gary Mendoza ' ' Application.EnableCancelKey = x1Disabled Application.ScreenUpdating = False ActiveWorkbook.Unprotect Password:="lindAP" Sheets("WKLY-RPT").Visible = True Sheets("WKLY-RPT").Unprotect Password:="lindAP" Sheets("WKLY-RPT").select ActiveWindow.LargeScroll ToRight:=3 Range("A1:K51").select Range("A1").Activate activesheet.PageSetup.PrintArea = "$A$1:$K$51" ActiveWindow.SelectedSheets.PrintPreview Sheets("WKLY-RPT").Visible = False ActiveWorkbook.Protect Structu=True, Windows:=False, Password:="lindAP" Sheets("EXP RPT").Visible = True Sheets("EXP RPT").select Range("K10").select Application.ScreenUpdating = False Application.EnableCancelKey = x1Interrupt End Sub |
#3
|
|||
|
|||
Thanks for your response. Basically, I'm focusing on the categories with
multiple rows. For example, currently wksht 1 (entitled "EXP RPT") always displays 3 rows (B14:B16) for a specific category, while the print-out version of wksht 2 (entitled "WKLY RPT") summarizes these rows into one row (DM14). Now I'd like for wksht 2 to always show/print at least 1 row (DM14) for this category. However, whenever the User populates the additional rows (i.e., B15 and B16) I'd like for wksht 2 to correspond and insert the same number rows with matching info (i.e., DM15 and DM16). Now wksht 1 also shows 3 other categories with multiple rows ( B19:b25, B34:B38 and B55:B59), but each rows contains a drop down list (via "Data Validation"). Once again, I'd like for wksht 2 to always print at least 1 rows for each of these categories (3) and only insert additional rows when populated on wksht 1. Now if it's easier, I can increase the number of rows on wksht 2 for these categories to match the number of rows shown on wkst 1, therefore rather than the macro inserting populated rows it would be hiding/deleting blank rows them. "Otto Moehrbach" wrote: Question 1: Yes. It's rather simple really. But how does Excel know how many rows to add ("Currently, each category is assigned 1 row, while other categories are assigned several rows.")? Question 2: Yes, you can add code to the macro to massage the range any way you want before printing. But you will have to furnish more detail about your data layout and what blank rows you want deleted before printing. All of them? HTH Otto "Mr. G." <Mr. wrote in message ... I currently linked worksheet 1 to a hidden and protected worksheet 2. Wksht 1 allows the user to select from a list of categories using a drop-down menu I set-up via "Data Validation". Each row of wksht 1 corresponds to a category. My question is 2 part: 1) Is it possible to have wksht 1 insert a new row automatically everytime a different category is selected from the drop down menu? Basically I'm trying to reduce the amount of rows appearing on the screen when not utilized. Currently, each category is assigned 1 row, while other categories are assigned several rows. Depending on the User, categories with multiple rows are often not applicable and are thus left blank. 2) Wksht 2 is what the User prints via macro button from wksht 1. I'd like for wksht 2 (hidden/protected) to reflect the new rows added to wksht 1. Basically, I'm trying to omit certain blank rows before printing. Does this have to be done via a formula or macro? If macro, can my existing macro (print button) be modified to filter out the blank rows before printing? Here's an example of what my current macro reads: Sub Macro10() ' Macro1 Macro ' Macro recorded 11/14/2004 by Gary Mendoza ' ' Application.EnableCancelKey = x1Disabled Application.ScreenUpdating = False ActiveWorkbook.Unprotect Password:="lindAP" Sheets("WKLY-RPT").Visible = True Sheets("WKLY-RPT").Unprotect Password:="lindAP" Sheets("WKLY-RPT").select ActiveWindow.LargeScroll ToRight:=3 Range("A1:K51").select Range("A1").Activate activesheet.PageSetup.PrintArea = "$A$1:$K$51" ActiveWindow.SelectedSheets.PrintPreview Sheets("WKLY-RPT").Visible = False ActiveWorkbook.Protect Structu=True, Windows:=False, Password:="lindAP" Sheets("EXP RPT").Visible = True Sheets("EXP RPT").select Range("K10").select Application.ScreenUpdating = False Application.EnableCancelKey = x1Interrupt End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
Simultaneouly insert of rows or col. - Ajit | Excel Discussion (Misc queries) | |||
INSERT ROWS WITHOUT SCREWING-UP FORMULAS! | Excel Worksheet Functions | |||
Challenging Charting | Charts and Charting in Excel | |||
Auto Insert of Rows | Excel Worksheet Functions |