Printing Issue

[Darren Smith]

I am trying to print ranges based on the last row number and the Listbox selection.

It gets down to this line then it says the compile error wrong number of arguments???

Set Rng = ws.Range("A1:AA61", "A62:AA122", "A123:AA183")

Full code:

Private Sub Print_Job_Card_Click()

Dim i As Long, C As Long
Dim ws As Worksheet
Dim Rng As Range
Dim SheetArray() As String, SelectedItems As String
Dim x As Integer
Dim LRow As Long

Set ws = ThisWorkbook.Worksheets("Job Card Master")

    On Error Resume Next
    With Me.Print_Listbox
    For i = 0 To .ListCount - 1
    If .Selected(i) Then
    SelectedItems = SelectedItems & Me.Print_Listbox(i)
    ReDim Preserve SheetArray(C)
    SheetArray(C) = .List(i)
    C = C + 1
   
    If SelectedItems = "Job Card with Time Analysis" Then
   
    With ws

    LRow = .Cells(Rows.Count, 5).End(xlUp).Row
     
      If LRow = 66 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA66")
      End If
     
      If LRow = 125 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA61", "A62:AA125")
      End If
     
      If LRow = 183 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA61", "A62:AA122", "A123:AA183")
      End If
     
      If LRow = 244 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA61", "A62:AA122", "A123:AA183", "A184:AA244")
      End If
     
      If LRow = 307 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA61", "A62:AA122", "A123:AA183", "A184:AA244", "A145:AA307")
      End If
      Rng.PrintOut
     
      End If
     
      End With
      End With

            Next i
            Sheets(SheetArray()).PrintOut

End Sub

Function PAForJobCard()
       With ws.PageSetup
      .LeftMargin = Application.InchesToPoints(0.2)
      .RightMargin = Application.InchesToPoints(0.2)
      .TopMargin = Application.InchesToPoints(0.2)
      .BottomMargin = Application.InchesToPoints(0.2)
      .HeaderMargin = Application.InchesToPoints(0.2)
      .FooterMargin = Application.InchesToPoints(0.2)
      .Orientation = xlLandscape
      .PaperSize = xlPaperA3
      End With
End Function

 

[John_w]

It gets down to this line then it says the compile error wrong number of arguments???

Set Rng = ws.Range("A1:AA61", "A62:AA122", "A123:AA183")

Change it to:

Set Rng = ws.Range("A1:AA61,A62:AA122,A123:AA183")

 

[Darren Smith]

Thanks, that's very good but why is Next i saying Next without For

Private Sub Print_Job_Card_Click()


Dim i As Long, C As Long
Dim ws As Worksheet
Dim Rng As Range
Dim SheetArray() As String, SelectedItems As String
Dim x As Integer
Dim LRow As Long

Set ws = ThisWorkbook.Worksheets("Job Card Master")

    On Error Resume Next
    With Me.Print_Listbox
    For i = 0 To .ListCount - 1
    If .Selected(i) Then
    SelectedItems = SelectedItems & Me.Print_Listbox(i)
    ReDim Preserve SheetArray(C)
    SheetArray(C) = .List(i)
    C = C + 1
    Next i
   
    If SelectedItems = "Job Card with Time Analysis" Then
   
    With ws

    LRow = .Cells(Rows.Count, 5).End(xlUp).Row
     
      If LRow = 66 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA66")
      End If
     
      If LRow = 125 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA61,A62:AA125")
      End If
     
      If LRow = 183 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA61,A62:AA122,A123:AA183")
      End If
     
      If LRow = 244 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA61,A62:AA122, A123:AA183,A184:AA244")
      End If
     
      If LRow = 307 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA61,A62:AA122,A123:AA183,A184:AA244,A145:AA307")
      End If
      Rng.PrintOut
          Next i

     
      End With
     

           
            Sheets(SheetArray()).PrintOut

End Sub

 

[NoSparks]

Which Next i
the first one or the second one ?

 

[John_w]

Which Next i
the first one or the second one ?

You only have one For i = loop and If .Selected(i) Then doesn't have a matching End If before the first Next i

I'm not sure of the intended logic, but you have mismatched For ... Next, With...End With and If ... End If blocks. Use proper indentation for every such block and you should see whether the code structure is correct or not.

 

[Darren Smith]

Thanks all works fine now