Print sheets depend on cell value

[KlausW]

Hi everyone

I use this VBA code to print tabs, except those in the VBA code. Now I would like to make it so that the VBA code only prints from the number in cell L1. Example I write 11 in L1, only tabs 11, 12, 13,14 up to 30 should be printed, if it says 2 in L1 it should only be tabs 2, 3,4,,5 up to 30. Of course there are not the same number of days in all months so some will also have 31.
I hope this makes sense, and some can help.
All help will be appreciated.

Best regards
Klaus W

Sub Rektangelafrundedehjørner1_Klik()

Dim ws As Worksheet
 For Each ws In ActiveWorkbook.Worksheets
  If ws.Visible = True Then
  If ws.Name <> "Ark1" And _
    ws.Name <> "Stamdata" And _
    ws.Name <> "Ialt" And _
    ws.Name <> "Til BM" And _
    ws.Name <> "PRB" Then
       With ws.PageSetup
        .PrintArea = "a1:r20" ' USE YOUR PRINTAREA
        .Zoom = False
        .FitToPagesTall = 1
        .FitToPagesWide = 1
       End With
       ws.PrintOut
   End If
  End If
 Next ws

End Sub

 

[Joe4]

You did not specify which sheet you are looking at for the number in cell L1. I am assuming it is coming from the first sheet in your workbook, but you may need to change that to suit your needs.

See if this code works for you:

Sub MyPrintMacro()

    Dim wsc As Long
    Dim st As Long
    Dim i As Long
    
'   Get starting worksheet number from cell L1 on sheet1
    st = Sheets(1).Range("L1")
    
'   Get total count of worksheeets
    wsc = Worksheets.Count
    
'   Loop through worksheets, from starting one in L1 to the end
    For i = st To wsc
        With Sheets(i).PageSetup
            .PrintArea = "a1:r20" ' USE YOUR PRINTAREA
            .Zoom = False
            .FitToPagesTall = 1
            .FitToPagesWide = 1
        End With
        Sheets(i).PrintOut
    Next i
    
End Sub

 

[Joe4]

Note that my previous code works off of Sheet indexes. If your are actually trying to work off the sheet names, and not the indexes, and the sheet names and indexes do not line up (i.e. the 2nd sheet in your workbook is not actually named "2"), then you might need to use this version of the code instead, which works off of the sheet names:

Sub MyPrintMacro2()

    Dim fst As String
    Dim lst As String
    Dim j As Long
    Dim k As Long
    Dim i As Long
    Dim st As String
    
'   Get starting worksheet number from cell L1 on sheet1
    fst = Sheets(1).Range("L1")
    
'   Get name of last sheet
    lst = Sheets(Sheets.Count).Name
    
'   Convert fst and lst to numbers
    j = CInt(fst)
    k = CInt(lst)
    
'   Loop through worksheets, from starting one in L1 to the end
    For i = j To k
'       Convert number back to string
        st = CStr(i)
        With Sheets(st).PageSetup
            .PrintArea = "a1:r20" ' USE YOUR PRINTAREA
            .Zoom = False
            .FitToPagesTall = 1
            .FitToPagesWide = 1
        End With
        Sheets(st).PrintOut
    Next i
    
End Sub

 

[KlausW]

Note that my previous code works off of Sheet indexes. If your are actually trying to work off the sheet names, and not the indexes, and the sheet names and indexes do not line up (i.e. the 2nd sheet in your workbook is not actually named "2"), then you might need to use this version of the code instead, which works off of the sheet names:

Sub MyPrintMacro2()

    Dim fst As String
    Dim lst As String
    Dim j As Long
    Dim k As Long
    Dim i As Long
    Dim st As String
   
'   Get starting worksheet number from cell L1 on sheet1
    fst = Sheets(1).Range("L1")
   
'   Get name of last sheet
    lst = Sheets(Sheets.Count).Name
   
'   Convert fst and lst to numbers
    j = CInt(fst)
    k = CInt(lst)
   
'   Loop through worksheets, from starting one in L1 to the end
    For i = j To k
'       Convert number back to string
        st = CStr(i)
        With Sheets(st).PageSetup
            .PrintArea = "a1:r20" ' USE YOUR PRINTAREA
            .Zoom = False
            .FitToPagesTall = 1
            .FitToPagesWide = 1
        End With
        Sheets(st).PrintOut
    Next i
   
End Sub

Thanks it not work, I got a error in this line. KW

j = CInt(fst)

 

[ebea]

Du er nødt til, Klaus, at lave dine faner så du har dem nummeret korrekt, til at følge datoer i måneden, som jeg kan læse er din tanke med at udskrive disse faner (Ark). Ellers vil det blive et roderi af dine dage.
Klaus, you have to get your Sheets in correct order, so they reflect the day in Month, which I can read are your intension for the print jobs; and for this, Joe's code in #2 will work. Else it will be a mess!

 

[Joe4]

Thanks it not work, I got a error in this line. KW

j = CInt(fst)

What exactly do you have in cell L1?

Are all your sheet names, starting from the value in cell L1 to the last sheet names exactly numbers, i.e. 1, 2, 3, 4?

 

[KlausW]

Du er nødt til, Klaus, at lave dine faner så du har dem nummeret korrekt, til at følge datoer i måneden, som jeg kan læse er din tanke med at udskrive disse faner (Ark). Ellers vil det blive et roderi af dine dage.
Klaus, you have to get your Sheets in correct order, so they reflect the day in Month, which I can read are your intension for the print jobs; and for this, Joe's code in #2 will work. Else it will be a mess!

Jeg har lige omdøbt alle ark til de rigtig navne men for stadig fejl i linjen. I just renamed all the sheets to the correct names but still getting errors in the line

j = CInt(fst)

.

 

[KlausW]

What exactly do you have in cell L1?

Are all your sheet names, starting from the value in cell L1 to the last sheet names exactly numbers, i.e. 1, 2, 3, 4?

There is a number and the cell is formatted as a number. No, in cell L1 it now says 11. I would also print tabs 11 to 30. But as before, I get the error.

 

[KlausW]

What exactly do you have in cell L1?

Are all your sheet names, starting from the value in cell L1 to the last sheet names exactly numbers, i.e. 1, 2, 3, 4?

I don't know if this image can help. Cell L1 is in sheets40, (Stamdata)

 

[Joe4]

If the value in cell L1 is already numeric, just change this line in my VBA code:

j = CInt(fst)

to this:

j = fst