Change a cells data then Print based on print area pages?

[Saltioh]

Hey Guys,

Not sure how to word my question, I'm trying to make a stillage sheet for a sheet metal company, I'm trying to get it so the sheet will insert the stillage number into a cell, then print the first page of the print area, then change the same cell as the original to the next stillage's number, then print the 2nd page of the print area. I can provide some image examples if required.

Thanks in advance

 

[EXCEL MAX]

Hello Saltioh, welcome to us.
Hope so you are know something about VBA, because it will be needed.
If I understand you want every time you change value in the some cell ("A1" in this case) automaticaly print this page.
If I'm right you can try in this way.
Open VBA editor and in the sheet module insert this code.

Private Sub Worksheet_Change(ByVal Target As Range)

    vFromN = ActiveSheet.Range("A1").Value
    vToN = vFromN
    If IsNumeric(vFromN) And vFromN > 0 And _
        Not vFromN > ActiveSheet.PageSetup.Pages.Count Then _
        ActiveWindow.SelectedSheets.PrintOut _
        vFromN, vToN, 1, True, , , True, , False

End Sub

 

[Saltioh]

Hey, thanks for the response.

This looks very close to what I trying to achieve, Is there a way that this code can, for example, Cell A1 has "1", then print page 1, then change the data itself in Cell A1 to 2, then print page 2? and so on, the numbers it prints be from a list? eg, 1, 2, 3, 6, 14, P.

Is there a way to have P represent a Page number?

Sorry if this seems overly complicated.

 

[EXCEL MAX]

Seems not complicated, but where will be list with pages for printing stored?

 

[Saltioh]

Where would be the best place? Can it be within the Code? Or Is it best they are held within the worksheet somewhere? in cells perhaps?

 

[EXCEL MAX]

Yes, the pages can be stored in the currently excel file, if they will be always same numbers.
If you want to change pages numbers, they can taken from worksheet cells.

 

[Saltioh]

Fantastic, How would I adapt your original code to do that?

Thank you so much for your help.

 

[EXCEL MAX]

The better solution will be to have command button to do job.
Do to Developer tab and insert button. Set button to run this code.

Sub PrintPages()

    Dim vPages, vPage, vN As Integer
   
    With ActiveSheet.Range("A1")
        vPages = Split(.Value, "/")
        For vN = 0 To UBound(vPages)
            If IsNumeric(Trim(vPages(vN))) Then
                vPage = CInt(Trim(vPages(vN)))
                If vPage > 0 And Not vPage > _
                    ActiveSheet.PageSetup.Pages.Count Then _
                    ActiveWindow.SelectedSheets.PrintOut _
                    vPage, vPage, 1, True, , , True, , False
            End If
        Next vN
    End With
    
End Sub

In the cell "A1" input number of the pages, splited by slash("2/3/5" for example),
that you want to print and run command button.
To avoid mistakes take care the values entered are correct format.
When you finish testing replace this part of code...

ActiveSheet.PageSetup.Pages.Count Then _
ActiveWindow.SelectedSheets.PrintOut _
vPage, vPage, 1, True, , , True, , False

with this...

ActiveSheet.PageSetup.Pages.Count Then _
ActiveWindow.SelectedSheets.PrintOut _
vPage, vPage, 1, False, , , True, , False