#1   Report Post  
Mr. G.
 
Posts: n/a
Default 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   Report Post  
Otto Moehrbach
 
Posts: n/a
Default

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   Report Post  
Mr. G.
 
Posts: n/a
Default

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




  #4   Report Post  
Otto Moehrbach
 
Posts: n/a
Default

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 . Remove the "nop" from
this address. HTH Otto
"Mr. G." <Mr. wrote in message
...
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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert rows based on specific value bob Excel Worksheet Functions 6 February 29th 08 08:11 PM
Simultaneouly insert of rows or col. - Ajit Ajit Munj Excel Discussion (Misc queries) 1 March 23rd 05 10:17 AM
INSERT ROWS WITHOUT SCREWING-UP FORMULAS! BILL GATES - I WANNA WORD WITH YOU! Excel Worksheet Functions 1 March 4th 05 02:32 PM
Challenging Charting C TO Charts and Charting in Excel 0 January 17th 05 07:57 PM
Auto Insert of Rows Gar3th Excel Worksheet Functions 0 January 6th 05 09:33 PM


All times are GMT +1. The time now is 11:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"