Hello everyone.

Can someone modify my code below my data is only working on 1 sheet and what I wanted now is to array and add another sheet on my code.

Private Sub CommandButton1_Click()
ActiveWindow.SelectedSheets.PrintPreview
End Sub

Private Sub CommandButton2_Click()
Dim ws As Worksheet
Dim LastRow As Long
Dim Rng As Range

Set ws = Sheet2

With ws
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

For Each Rng In .Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible)
Sheet1.Range("J8") = Rng
ActiveWindow.SelectedSheets.PrintPreview
Next
End With

End Sub

Private Sub Worksheet_Activate()
Dim ws As Worksheet

Set ws = Sheet2

With ws

LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
End With

Sheet1.CommandButton2.Caption = "Print All " & _
WorksheetFunction.Subtotal(3, Sheet2.Range("A2:A" & LastRow)) & _
" Records"


Dim NameAry() As Variant
Dim A As Long
With ws

For A = 2 To LastRow
ReDim Preserve NameAry(A)
NameAry(A) = Replace(.Range("P" & A), ",", " - ")
Next
End With

With Sheet1.Range("J8").Validation
.Delete
.Add xlValidateList, , , Join(NameAry, ",")
Application.Calculate
End With
End Sub