Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Sheet & Range Page Setup & Printing
Hi Team
Have been playing with this for the past hour or so and require your help please. Now, I use the following to select a worksheet based on a fixed cell value: If Not ws.Cells(myCell, "A").Value Is Nothing Then Sheets("" & myCell).Activate. I am trying to incorporate this into my Dynamic Print Sheet selection, alas it does not work. Essentially, the code looks @ If ws.Cells(i, "H").Value = "Y", if it does then go to that sheet.name and set it up for .PrintPreview/.Printing. Dim ws As Worksheet Dim i, ii, Count, lRow As Long Dim myRng As Range ii = 51 Set ws = Sheets("Fleet Summary") For i = 5 To 50 If Not ws.Cells(i, "H").Value = "Y" Then Exit For With ws If Not .Cells(i, "A").Value Is Nothing Then Sheets("" & i).Activate End If End With With ActiveSheet lRow = .Range("A" & .Rows.Count).End(xlUp).Row Count = lRow Set myRng = .Range("A1", "K" & Count) If lRow < 70 Then .ResetAllPageBreaks .PageSetup.PrintArea = myRng.Address While Count 0 And ii < lRow If Count 51 Then .HPageBreaks.Add befo=.Rows(ii) End If Wend End If .PageSetup.PaperSize = xlPaperA4 .PageSetup.Orientation = xlLandscape .PageSetup.FitToPagesWide = 1 .PageSetup.FitToPagesTall = False .PageSetup.LeftMargin = Application.CentimetersToPoints(28.347) .PageSetup.RightMargin = Application.CentimetersToPoints(28.347) .PageSetup.TopMargin = Application.CentimetersToPoints(28.347) .PageSetup.BottomMargin = Application.CentimetersToPoints(28.347) .PageSetup.HeaderMargin = Application.CentimetersToPoints(0) .PageSetup.FooterMargin = Application.CentimetersToPoints(0) .PageSetup.PrintTitleRows = "$1:$7" .PageSetup.PrintTitleColumns = "" .PageSetup.LeftHeader = "" .PageSetup.CenterHeader = "" .PageSetup.RightHeader = "" .PageSetup.LeftFooter = "" .PageSetup.CenterFooter = "" .PageSetup.RightFooter = "" .PageSetup.PrintHeadings = False .PageSetup.CenterHorizontally = True .PageSetup.CenterVertically = False .PrintPreview End With Next i TIA Cheers Mark. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Sheet & Range Page Setup & Printing
Hi Mark,
I would break this up so that PageSetup is a reusable procedure that receives an array (or delimited list) of sheet names to act upon. You could also 'Group' sheets and do PageSetup on the group (selected sheets). Here's some food for thought... Option Explicit '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''' ' GroupSheets() ' This procedure requires only the necessary amount of coding be used ' in the Caller. By default, it requires passing only the first arg. ' Use Example: GroupSheets "Sheet1,Sheet3" ' creates a group of only those sheets. ' To group all sheets in a workbook except those sheets: ' GroupSheets "Sheet1,Sheet3", False ' To group all sheets in a workbook pass an empty string: ' GroupSheets "", False ' You can pass the Wkb arg to specify any open workbook. ' (The Wkb doesn't need to be active for this purpose) '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''' Public Sub GroupSheets(Sheetnames As String, _ Optional bInGroup As Boolean = True, _ Optional wkb As Workbook) ' Groups sheets in Wkb based on whether Sheetnames ' are to be included or excluded in the grouping. ' Arg1 is a comma delimited string. (ie: "Sheet1,Sheet3") Dim Shts() As String, sz As String Dim i As Integer, wks As Worksheet, bNameIsIn As Boolean If wkb Is Nothing Then Set wkb = ActiveWorkbook For Each wks In wkb.Worksheets bNameIsIn = (InStr(Sheetnames, wks.name) 0) If bInGroup Then If bNameIsIn Then sz = wks.name Else If bNameIsIn Then sz = "" Else sz = wks.name End If If Not sz = "" Then '//build the array ReDim Preserve Shts(0 To i): Shts(i) = sz: i = i + 1 End If Next wkb.Worksheets(Shts).Select End Sub 'GroupSheets Sub TestGroupSheets() Dim wks As Worksheet GroupSheets "Sheet1,Sheet2,Sheet3" For Each wks In ActiveWindow.SelectedSheets wks.Activate: ActiveWindow.DisplayOutline = False Next wks End Sub Sub TestGroupSheets2() Dim avWks As Variant avWks = Array("Sheet1", "Sheet2", "Sheet3") Dim i As Integer For i = LBound(avWks) To UBound(avWks) Sheets(avWks(i)).Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 Next End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Sheet & Range Page Setup & Printing
Hi Garry
Apologies for my delay in responding along with being a pain, and thank you for your time. Forgive me if I have not explained accurately what it is I am attempting to do. The user will generate multiple route sheets via a cmdBtn coded to do just this. These generated sheets will be tacked onto the end of existing sheets ( permanent sheets ) within the Wb. The sheet.names will always be the same, it's just the combination will vary from day to day depending on whether or not the route has any work, or driver to actually do it. The Array Grouping would need to be dependent on either this "Y" criteria in the Fleet Summary sheet, otherwise based off a "Permanent Sheet" exclusion list. Permanent Sheets Array("Menu", "Import", "Conversion", "Fleet Summary", "Template", "Break Table"). Then your Ws loop thru code would be awesome, as opposed to the change in the Grouping each day. As always, thank you so much for your help. Cheers Mark. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Sheet & Range Page Setup & Printing
Hi Garry
Apologies for my delay in responding along with being a pain, and thank you for your time. Forgive me if I have not explained accurately what it is I am attempting to do. The user will generate multiple route sheets via a cmdBtn coded to do just this. These generated sheets will be tacked onto the end of existing sheets ( permanent sheets ) within the Wb. The sheet.names will always be the same, it's just the combination will vary from day to day depending on whether or not the route has any work, or driver to actually do it. The Array Grouping would need to be dependent on either this "Y" criteria in the Fleet Summary sheet, otherwise based off a "Permanent Sheet" exclusion list. Permanent Sheets Array("Menu", "Import", "Conversion", "Fleet Summary", "Template", "Break Table"). Then your Ws loop thru code would be awesome, as opposed to the change in the Grouping each day. As always, thank you so much for your help. Cheers Mark. You could have the code select the sheets into a list that the user can filter. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range Setup... | Excel Worksheet Functions | |||
Page Setup Printing | Excel Discussion (Misc queries) | |||
can VBA be used to set page setup for printing? | Excel Discussion (Misc queries) | |||
Calculate page setup zoom value for 1 to 1 printing | Excel Programming | |||
Excel Printing and Page Setup | Excel Discussion (Misc queries) |