Solved Print a working WS for a number of names all at once

November 14, 2016 at 04:29:00
Specs: Windows 64
Hi Guys,
A more indepth explanation of what i am trying to do

WS 'A' has a drop down list of a number of names
I select a name, click on a Form Control Button that then takes me to WS'B'
WS 'B' is a sheet with information on.
On WS 'B', I click another Forms control button to print the WS. This in turn closes WS 'B, and takes me back to WS 'A'
I repeat this process for all the names appearing in the drop down box

I would like to do the whole of the above process in one click of a Forms Control Button

DerbyDad03, if you happen across this post, and still have the workbook I sent you, you will be able to see what I am doing
Dashboard
Player Analysis
Select a name
View Statistical Graphs

Regards
Tony


See More: Print a working WS for a number of names all at once

Report •

✔ Best Answer
November 15, 2016 at 08:08:38
Hi there,
Have had a bit of time so managed to cobble together the following. Seems to work as I require. Could you have a look over and highlight anything that I could have done simpler
Cheers
Tony
Sub ShowGraphs22()

    Application.ScreenUpdating = False
    
    If MsgBox("YOU ARE ABOUT TO PRINT THE GRAPHS FOR THE AGM. ARE YOU SURE?", vbYesNo) = vbYes Then
    
' Declare variables.
    Dim MyRange As Range
    Dim MyCell As Range
' Define the target Range.
    Set MyRange = Sheets("GRAPHS1").Range("AB6:AB10")
' Start looping through the range.
    For Each MyCell In MyRange
' Do something with each cell.
    If MyCell.Value = "" Then
        MsgBox ("ALL GRAPH CHARTS HAVE BEEN PRINTED")
     Exit Sub
  
     Else
       
    Sheets("GRAPHS1").Visible = True
    Sheets("GRAPHS1").Select
    
' unprotect
    
    Sheets("GRAPHS1").Range("A2") = MyCell.Value
' Change Axis Scales

    With ActiveSheet.ChartObjects("Chart 1").Chart

        ' Category (X) Axis
        With .Axes(xlCategory)
            .MaximumScale = ActiveSheet.Range("C54").Value
            .MinimumScale = ActiveSheet.Range("C55").Value
            .MajorUnit = ActiveSheet.Range("C57").Value
        End With

        ' Value (Y) Axis
        With .Axes(xlValue)
            .MaximumScale = ActiveSheet.Range("C52").Value
            .MinimumScale = ActiveSheet.Range("C53").Value
            .MajorUnit = ActiveSheet.Range("C56").Value
        End With

    End With
    End If
    
' prints sheet as required

    Range("F1:Y83").Select
    ActiveSheet.PageSetup.PrintArea = "$F$1:$Y$83"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
        
    Sheets("GRAPHS1").Visible = False
    
' protect
'Step 5: Get the next cell in the range
    Next MyCell
    
    End If
    
    Application.ScreenUpdating = True
    
End Sub



#1
November 14, 2016 at 06:49:51
This doesn't seem difficult.

are the names in the dropdown the same as the WS name?

if so a simple loop through each WS from the dropdown would surfice with a call to the print function.

I just need to understand what you mean by

This in turn closes WS 'B, and takes me back to WS 'A'

if Derby is already on it then fair enough otherwise PM me and I will send you my email address so I can have a look at the workbook too.


Report •

#2
November 15, 2016 at 01:15:37
No, the name of the worksheet is 'GRAPHS'
The name that is selected from the drop down box 'BOARD!E5 appears in GRAPHS!C5
I click on a forms Button and WS GRAPHS is unhidden
The WS GRAPHS then amasses the information required for the name from the dropdown box.
I then click on a Forms button that prints the work sheet, hides the WS GRAPHS and returns me to the dropdown box on WS BOARDS
I then repeat this for the next name in the drop down box, and so on

If its of help, the list of names are in a range of A1:A48 on another worksheet

If you could supply me with an idea of how to write the loop, using the above range, but ignoring blanks, I think I could manufacture something that may work


Report •

#3
November 15, 2016 at 01:34:44
Without looking at the workbook I hestitate to give any code examples as this will mean having to debug something you put together that I might not understand or may not be 100% suitable.

Why not just send me the workbook, PM me and i'll give you the email address to send it to.


Report •

Related Solutions

#4
November 15, 2016 at 08:08:38
✔ Best Answer
Hi there,
Have had a bit of time so managed to cobble together the following. Seems to work as I require. Could you have a look over and highlight anything that I could have done simpler
Cheers
Tony
Sub ShowGraphs22()

    Application.ScreenUpdating = False
    
    If MsgBox("YOU ARE ABOUT TO PRINT THE GRAPHS FOR THE AGM. ARE YOU SURE?", vbYesNo) = vbYes Then
    
' Declare variables.
    Dim MyRange As Range
    Dim MyCell As Range
' Define the target Range.
    Set MyRange = Sheets("GRAPHS1").Range("AB6:AB10")
' Start looping through the range.
    For Each MyCell In MyRange
' Do something with each cell.
    If MyCell.Value = "" Then
        MsgBox ("ALL GRAPH CHARTS HAVE BEEN PRINTED")
     Exit Sub
  
     Else
       
    Sheets("GRAPHS1").Visible = True
    Sheets("GRAPHS1").Select
    
' unprotect
    
    Sheets("GRAPHS1").Range("A2") = MyCell.Value
' Change Axis Scales

    With ActiveSheet.ChartObjects("Chart 1").Chart

        ' Category (X) Axis
        With .Axes(xlCategory)
            .MaximumScale = ActiveSheet.Range("C54").Value
            .MinimumScale = ActiveSheet.Range("C55").Value
            .MajorUnit = ActiveSheet.Range("C57").Value
        End With

        ' Value (Y) Axis
        With .Axes(xlValue)
            .MaximumScale = ActiveSheet.Range("C52").Value
            .MinimumScale = ActiveSheet.Range("C53").Value
            .MajorUnit = ActiveSheet.Range("C56").Value
        End With

    End With
    End If
    
' prints sheet as required

    Range("F1:Y83").Select
    ActiveSheet.PageSetup.PrintArea = "$F$1:$Y$83"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
        
    Sheets("GRAPHS1").Visible = False
    
' protect
'Step 5: Get the next cell in the range
    Next MyCell
    
    End If
    
    Application.ScreenUpdating = True
    
End Sub


Report •

Ask Question