IF and Printing Options

Hi Matt,

I am sorry.

I haven't read your questions well.

I will be back with another code soon.



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Hi Matthew,

Please have a look at the code below. this will only select visible sheets listed in a Array function. I had to pass the sheet names to a function IsInArray to get it right.

I hope this logic can get your code right.



Sub SelectSheets()
Dim myArray() As Variant
Dim i As Integer
Dim j As Integer
j = 0
For i = 1 To Sheets.Count
If Sheets(i).Visible = True And IsInArray(Sheets(i).Name, Array("Sheet1", "Sheet2", "Sheet3")) Then
ReDim Preserve myArray(j)
myArray(j) = Sheets(i).Name
j = j + 1
End If
Next i
Sheets(myArray).Select
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant)
IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))


End Function





Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Hi Matthew,

Please have a look at the code below.

I cannot test it without having the workbook.

It is working in my workbook.

See link below for more print options.

http://www.excelhowto.com/macros/print-worksheets-vba/


Private Sub CommandButton5_Click()
Dim myArray() As Variant
Dim i As Integer
Dim j As Integer

If Sheets("Draft Phase 3").Visble = True Then
j = 0
For i = 1 To Sheets.Count
If Sheets(i).Visible = True And IsInArray(Sheets(i).Name, Array("Draft Phase 1", "Draft Phase 2", "Draft Phase 3", "Buildcost Phase 1", "Buildcost Phase 2", "Buildcost phase 3")) Then
ReDim Preserve myArray(j)
myArray(j) = Sheets(i).Name
j = j + 1
End If
Next i
Sheets(myArray).PrintOut

Sheets("Sheet1").Select
ElseIf Sheets("Draft Phase 3").Visble = False Then
j = 0
For i = 1 To Sheets.Count
If Sheets(i).Visible = True And IsInArray(Sheets(i).Name, Array("Draft Phase 1", "Draft Phase 2", "Buildcost Phase 1", "Buildcost Phase 2")) Then
ReDim Preserve myArray(j)
myArray(j) = Sheets(i).Name
j = j + 1
End If
Next i
Sheets(myArray).PrintOut


Sheets("Draft Phase 1").Select
ElseIf Sheets("Draft Phase 2").Visble = False Then

j = 0
For i = 1 To Sheets.Count
If Sheets(i).Visible = True And IsInArray(Sheets(i).Name, Array("Draft Phase 1", "Buildcost Phase 1")) Then
ReDim Preserve myArray(j)
myArray(j) = Sheets(i).Name
j = j + 1
End If
Next i
Sheets(myArray).PrintOut



Sheets("Draft Phase 1").Select


End If

End Sub




Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector