Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 30th 20, 08:26 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2020
Posts: 16
Default 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   Report Post  
Old April 30th 20, 09:43 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,184
Default 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   Report Post  
Old May 3rd 20, 03:56 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2020
Posts: 16
Default 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   Report Post  
Old May 3rd 20, 07:10 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,184
Default 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
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
Dynamic Range Setup... Trevor Williams[_2_] Excel Worksheet Functions 8 July 29th 09 04:05 PM
Page Setup Printing Sharon Excel Discussion (Misc queries) 0 December 6th 06 02:55 PM
can VBA be used to set page setup for printing? Dave F Excel Discussion (Misc queries) 2 November 21st 06 06:27 PM
Calculate page setup zoom value for 1 to 1 printing mgill Excel Programming 0 March 9th 05 08:17 PM
Excel Printing and Page Setup Sherry Excel Discussion (Misc queries) 1 January 25th 05 09:38 PM


All times are GMT +1. The time now is 09:18 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017