0

This is supposed to be a relatively simple function - an excel workbook with a selection of sheets to be filled out. If the user chooses to omit some of the sheets they will not be included in the final function.

It also hides a couple of columns in specific sheets which contain information not required on the exported format.

Sub Export()


Dim i As Integer, c As Integer, f As Integer, g As Integer, h As Integer, numberofsheets As Integer

' i is for counting total number of build sheets
' c is used in the while loop to track the current sheet the code is running on
' f is used to denote the row on the excel sheet currently look at
' g tracks the current sheet out of number of sheets marked for build
' h tracks consecutive sheets so the code doesnt run on the same one multiple times
' numberofsheets is the number of sheets required for final build

'printsheets is an array to denote which final sheets are to be exported

Dim sheetname As String             'name of current sheet

numberofsheets = Range("AC2")
ReDim printsheets(numberofsheets) As Variant

h = 1
i = Range("AC3").Value
For g = 1 To numberofsheets                 ' for loop run to create an array equal to number of sheets denoted for build
    c = h
    While (c < i)                           ' while loop to track which sheet is currently looked at
        f = (4 + c)
        sheetname = Range("AC" & f)
            If Range("AB" & f) = "Yes" Then     ' determines whether sheet is used for build
            printsheets(g - 1) = sheetname      ' adds sheet to array if used for build
                If Not sheetname = ("Quality Final") Then           'hides the button columns for basic build sheets, not quality final
                Worksheets(sheetname).Columns("L").EntireColumn.Hidden = True
                h = h + 1
                c = i
                Else: End If
            Else:
            h = h + 1
            c = c + 1
            End If
    Wend
Next g



ThisWorkbook.Sheets(printsheets()).Select


            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
            "C:\Build Sheets\MW\CT\PDF\" & ThisWorkbook.Name & ".pdf" _
            , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True



Sheets("Main Menu").Select
End Sub

the top part functions perfectly, it hides columns, and through testing I can see the array fills in properly. The problem comes on the select line.

If I specify a specific element of the array e.g. printsheets(3) then the function exports fine, but if I try and select multiple, or the entire array with printsheets() I get runtime error 9, subscript out of range.

I can't figure out where the problem is because I can select part of the array, but never the whole lot, which defeats the point of exporting everything into 1 sheet.

Pretty new to VBA so sorry if i missed something obvious!

BigBen
  • 46,229
  • 7
  • 24
  • 40
faucet37
  • 1
  • Double check that the sheet names in the array are spelled correctly - no typos, trailing spaces, etc. – BigBen Jan 30 '20 at 15:23
  • The only way I can reproduce your error is if you've got one of the sheet names wrong. – BigBen Jan 30 '20 at 15:28

1 Answers1

2

When the code runs into an error, choose debug and find the reference that is trying to be found within the array.

For example in the code put the cursor on 'numberOfsheet' if that is the array the code is getting stuck on), right click mouse and choose Add whatch. By holding the mouse over 'g' (the itemnumber in the array) it should show the number it is currently running. If its not, write in the debug window (ctrl + g) "Debug.Pring g" and hit enter to find out at which item of the array the code is running exactly. With that number you can find at which numberOfsheet (via add whatch) the code is stuck.

Likely this way you will find that the number (i, c, f, g, h or i) is larger then the array it is trying to find a reference in.

jonadv
  • 434
  • 6
  • 16
  • Except the problematic line is `ThisWorkbook.Sheets(printsheets()).Select` - which has no index. – BigBen Jan 30 '20 at 15:22
  • So thats the problem. – jonadv Jan 30 '20 at 15:25
  • No it should work fine if each element of that array is the name of a worksheet that exists in the workbook. You can select an entire array of sheets. – BigBen Jan 30 '20 at 15:25
  • To loop through the sheets use: for i = 0 to Ubound(printsheets()) //do stuff with printsheets(i) next i – jonadv Jan 30 '20 at 15:25
  • OP wants to select all the sheets to print out simultaneously, so you can't use a loop here. – BigBen Jan 30 '20 at 15:26
  • is that possible? Could the problem be with referring to 'ActiveSheet.'? – jonadv Jan 30 '20 at 15:29
  • 1
    Yes, very possible: https://stackoverflow.com/questions/14404650/save-multiple-sheets-to-pdf. This is an instance where you *want* to use `ActiveSheet`. – BigBen Jan 30 '20 at 15:29
  • In other words, as I mentioned in a comment to OP, the only way I can get the current code to fail is if there's a sheet name in the array that doesn't correspond to a sheet in the workbook. – BigBen Jan 30 '20 at 15:31
  • Thanks for the answer, it pointed out another flaw where my array was too big by 1! But I'm still getting the error where ```ThisWorkbook.Sheets(printsheets()).Select``` – faucet37 Jan 30 '20 at 15:32
  • BigBen if spelling the sheetname wroing was the case, then the code would get stuck on "Worksheets(sheetname).Columns("L").EntireColumn.Hidden = True" – jonadv Jan 30 '20 at 15:36
  • @faucet37 - if it's an array of sheet names, it should be a `String`, not a `Variant`. – BigBen Jan 30 '20 at 15:38
  • @i3ob - not necessarily - what if `If Range("AB" & f) = "Yes" Then` is not satisfied? That line will never be reached. – BigBen Jan 30 '20 at 15:39
  • 1
    printsheets(g - 1) = sheetname --> is it possible it is adding all the sheets or empty sheet names to the array? – jonadv Jan 30 '20 at 15:40
  • @BigBen then it tries to reference to Worksheets("Yes").Columns("L").etc but worksheet Yes doesnt exist, so it gets stuck. If it doesnt get stuck there, then that means that all the sheetnames exist – jonadv Jan 30 '20 at 15:41
  • 1
    @faucet37 - add a new loop and just `Debug.Print` all the sheet names in the array. Most likely you've got something in the array that shouldn't be there, or is misspelled. – BigBen Jan 30 '20 at 15:42
  • sheetname = Range("AC" & f) --> maybe also add Sheets(g).Activate to it, to make sure that Range("AC" &f) is referring to the sheet you are looping through. Or change it into Sheets(g).Range("AC" & f) – jonadv Jan 30 '20 at 15:44
  • I've ran it a few more times, and it seems it keeps getting stuck on the final string to be added to the array. It leaves it empty, but the cell references 100% contain yes, and the sheet name – faucet37 Jan 30 '20 at 15:44
  • So check what is in your array of printsheets() with the Add watch method – jonadv Jan 30 '20 at 15:48
  • I got there eventually! Thanks for the help! The problem was in the line ``` While (c < i)``` It was stopping 1 tick too early so the final bit of the array never got filled out. – faucet37 Jan 30 '20 at 15:59