Hi
This code works great up to the last stage. No matter what I try I get asubscript out of range. A few examples of what I have tried are shown in thecommented out code at the bottom. Ultimately, I want the sheet namesgenerated the code to be converted to a PDF. The sheet name index has alist of all the sheets in the workbook with a TRUE or FALSE in theadjacent column to determine if I should be included in the output.
I work in a reporting team so need this to be applicable to any report using atemplate hence not hard coding the sheet names. The watch window shows thefinal string matches exactly to how I would type it out explicitly and when I do the final lines of code work, so not sure why it doesn’t like this method.
This code works great up to the last stage. No matter what I try I get asubscript out of range. A few examples of what I have tried are shown in thecommented out code at the bottom. Ultimately, I want the sheet namesgenerated the code to be converted to a PDF. The sheet name index has alist of all the sheets in the workbook with a TRUE or FALSE in theadjacent column to determine if I should be included in the output.
I work in a reporting team so need this to be applicable to any report using atemplate hence not hard coding the sheet names. The watch window shows thefinal string matches exactly to how I would type it out explicitly and when I do the final lines of code work, so not sure why it doesn’t like this method.
Code:
Sub test()
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim wsI As Worksheet
Set wsI = wb.Sheets("SheetNameIndex")
Dim lr As Long
lr = wsI.Cells(Rows.Count, 1).End(xlUp).Row
Dim rngSht As Range
Set rngSht = wsI.Range("A2:A" & lr)
wsI.Select
rngSht.Select
Dim bRPT As Boolean
Dim strShts() As String
Dim strAry As String
Dim i As Integer
i = 0
For Each cell In rngSht
cell.Select
bRPT = ActiveCell.Offset(0, 1).Value
Select Case bRPT
Case True
i = i + 1
ReDim Preserve strShts(i)
strShts(i) = strShts(i) & """" & cell.Value & ""","
strAry = Join(strShts)
Case False
End Select
Next cell
strAry = Trim(Left(strAry, Len(strAry) - 2))
strAry = Trim(Right(strAry, Len(strAry) - 1))
' wb.Sheets(Array(strShts)).ExportAsFixedFormat Type:=xlTypePDF, Filename:="insert filename", OpenAfterPublish:=True
' wb.Sheets(Array(strAry)).Select
' wb.Sheets(Array(strAry)).Activate
End Sub