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
|