i have a list of sheets and in front a checkbox symbol is there whoose value is R
i need a code that selects all the sheets whose checkbox is R and create a pdf for all the selected sheets combine. here is my code but it is giving error subsript out of range on sheets(sharray).select
Code
Sub selectsheets()
Dim x As Long, i As Long, rCount As Long, sCount As Long
Dim mSh As Worksheet
Dim shArr() As Variant
Dim fSheet As String 'first shet in the range
Dim fRun As Boolean ' check if first sheet found
Const saveLoc As String = "C:\Users\user\Desktop\"
Const ExportName As String = "LDG CRUDE OIL.pdf"
Set mSh = Sheets("Cals")
rCount = WorksheetFunction.CountIf(mSh.Range("C:C"), "R")
fRun = False
sCount = 1
x = mSh.Range("A1").CurrentRegion.Rows.Count
ReDim shArr(1 To rCount)
For i = 1 To x
If mSh.Range("C" & i).Value = "R" Then
If fRun = False Then
fSheet = Sheets(i).Name
fRun = True
End If
shArr(sCount) = mSh.Range("A" & i).Value
sCount = sCount + 1
End If
Next i
Sheets(shArray).Select
Sheets(fSheet).Activate
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=saveLoc & ExportName
End Sub
Display More