Insert rows

[Mr. G.]

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 Structure:=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

 

[Otto Moehrbach]

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.]

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]

It would be easier to have the number of rows matching on the 2 sheets and
the code deleting the blank rows at the time of printing, but it can also be
done the other way also. Do you have formulas in the cells of the blank
rows to bring in the data from the other sheet? I ask this because deleting
the blank rows will delete the formulas. If you have formulas producing
blank cells, the code can just hide those rows and they won't print.
If you wish, send me, direct, a small file that shows what you have and what
you want. My email address is (e-mail address removed). Remove the "nop" from
this address. HTH Otto