Selecting Multiple Ranges based on criteria Excel VBA

[mvandeynze]

I am trying to create a code that will form a PDF report based on criteria entered in on a Userform. Based on the inputs, certain tables will need to be selected to copy to a new sheet and then export that new sheet into a PDF. I am having troubles creating the range to be copied.
Here is a part of the code that I am having trouble with.
I have Dim'd all the parts within the union brackets as a range and I have also set all those ranges as

Dim RPT, MBHWP, MBLWP, MBHAG, MBLAG, MBLOY, MBHOY, MBLTY, MBHTY as range

Set RPT = Sheets("CODE").Range("A1") 'this is a blank cell I used to set it because I read on a forum that your union needs to be defined first as something because it can't start as nothing.

With Sheets("Report")
Set MBHWP = .Range("A1129:E1175")
Set MBLWP = .Range("A1176:E1222")
Set MBHAG = .Range("A223:E1269")
Set MBLAG = .Range("A1270:E1316")
Set MBLOY = .Range("A1411:E1457")
Set MBHOY = .Range("A1458:E1504")
Set MBLTY = .Range("A1317:E1363")
Set MBHTY = .Range("A1364:E1410")
end with



Then I have the code for the selection:

If ComboBox2.Value = 40 Or ComboBox2.Value = 45 Then

If CheckBox1.Value = True Then
Set RPT = Union(MBHWP, RPT)
Else
If CheckBox2.Value = True Then
Set RPT = Union(MBLWP, RPT)
Else
If CheckBox3.Value = True Then
Set RPT = Union(MBHAG, RPT)
Else
If CheckBox4.Value = True Then
Set RPT = Union(MBLAG, RPT)
Else
If CheckBox5.Value = True Then
Set RPT = Union(MBLOY, RPT)
If CheckBox6.Value = True Then
Set RPT = Union(MBHOY, RPT)
Else
If CheckBox12.Value = True Then
Set RPT = Union(MBLTY, RPT)
Else
If CheckBox7.Value = True Then
Set RPT = Union(MBHTY, RPT)
Else
End If
End If
End If
End If
End If
End If
End If
End If
Else


I also have those named ranges in my name manager on the excel sheet as the same thing, but with "report" on the end. (example "MBHWPReport") and I have tried

Set RPT = Range(MBHWP, RPT) for all of them, and
Set RPT = Union(Range("MBHWPReport"), RPT) as well. (again with "MBHWPReport" being a named range on my excel sheet)


The idea is that if the user selected 40 for the combobox, it would look to the MB tables of the Report pages and select any of HWP, LWP, HAG, LAG, LTY, HTY, LOY, HOY based on if their checkbox was true in the user form. I do have other IFs for the combobox to correspond to other table sets of the Report page. I need to have any combination of these selects because the user could request any number of these and I need the code to adjust accordingly. However there is only one selection for the combobox (if they select 40, they will only be able to access the MB tables, if they want more they will have to run the code again with the other value in the combobox). The error I get is that the 'union' method or 'range' method is out of range. (depending on which one I used. I have searched multiple forum entries and have not found an answer that works for me.


Once I have all the ranges in RPT, This is the following code:

With ThisWorkbook
.Sheets.Add(After:=.Sheets("Report")).Name = "Name1"
End With

Range(RPT).Copy Destination:=Sheets("Name1").Range("A1")




Dim FName As Variant
FName = Application.GetSaveAsFilename( _
InitialFileName:="Name1.pdf", _
FileFilter:="PDF files, *.pdf", _
Title:="Export to pdf")
If FName <> False Then
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
End If


I am fairly new to VBA, I am self taught for work but I have caught on quickly with the help of these forums. However, I have not been able to find this exact problem on a forum and similar ones that I have based this code off of have not worked to fix this issue.
hope this all makes sense, thanks for all your help!

 

[JLGWhiz]

For clarification, the only one declared as a range is MBHTy. The rest will be considered Variant by VBA. Each variable must have the As keyword in the declarateion individually. You cannot group variables and use only one As statement.

 

[mvandeynze]

Thanks! I saw it done like that on another forum and assumed it was a form of correct notation.

However, that did not fix my problem, I am still getting the same error>

 

[JLGWhiz]

Based on the code posted, you do not need eithe the cell A1 reference or the Union function to do what you want. Union is to combine multiple non-contguous ranges into one. Your variables already define the the contiguous ranges so Union is not required in order to use them. Just use the original variable.

If CheckBox1.Value = True Then
 Set RPT = MBHWP
 ElseIf CheckBox2.Value = True Then
 Set RPT = MBLWP
 ElseIf CheckBox3.Value = True Then
 Set RPT = MBHAG
 ElseIf CheckBox4.Value = True Then
 Set RPT = MBLAG
 ElseIf CheckBox5.Value = True Then
 Set RPT = MBLOY
 ElseIf CheckBox6.Value = True Then
 Set RPT = MBHOY
 ElseIf CheckBox12.Value = True Then
 Set RPT = MBLTY
 ElseIf CheckBox7.Value = True Then
 Set RPT = MBHTY
 End If

 

[mvandeynze]

I want to be able to select multiple tables, so if checkbox 1,2,4, and 12 are chosen, I wish to chose MBHWP, MBLWP, MBLAG, MBLTY but if only one is chosen I wish to select just MBHWP.

 

[JLGWhiz]

I want to be able to select multiple tables, so if checkbox 1,2,4, and 12 are chosen, I wish to chose MBHWP, MBLWP, MBLAG, MBLTY but if only one is chosen I wish to select just MBHWP.

For the single ranges, see my post #4 . Where you have more than one checkbox selected for multiple non-contiguous ranges then some like,

If Checkbox1 = True And Checkbox2 = True  And Checkbox3 = True Then
         Set RPT = Union(ABCDE, EFGHi, JLMNB) 'These are fictitious but illustrate the point
End if

 

[mvandeynze]

I was hoping I would not have to go through each possible combination for the selections. As that would result in 127 different IF statement options, plus I need to copy those 127 IF checkbox options for each combobox option (which is 8 of them) so that would be 1016 different IF statements for my code. But I will start on that after lunch. Thanks

 

[mvandeynze]

My single procedure is too long for the macro, does anyone else have any ideas? I have already split the code into a separate procedure for each combobox option, however each combobox option's IF statement code is too long for the procedure. Each combobox option will be 1310 lines of code.

 

[mvandeynze]

https://www.experts-exchange.com/qu...-Ranges-Across-Multiple-Sheets-VBA-Excel.html

^this is basically what I want to do, but I'm not sure what the solution is saying.

 

[mvandeynze]

My union and range functions do not work,

When running the macro it stops at this line:
Set RPTA = Union(MBHWP, RPT)
which is the first union function of the page, as all 3 variable are set as ranges, should it not work? I can't understand why it doesn't.
the error comes up method of 'union' out of range

I've also tried
Set RPTA = Union(Range(MBHWP), Range(RPT))
although I shouldn't have to add the range function to it, then the debugger says method of 'range' out of range

ActiveSheet.calculate
    Dim WB As Workbook
    Set WB = ThisWorkbook
    Dim RPT As Range
    Dim RPTA As Range
    Dim RPTB As Range
    Dim RPTC As Range
    Dim RPTD As Range
    Dim RPTE As Range
    Dim RPTF As Range
    Dim RPTG As Range
    Dim RPTH As Range
   
Dim MBHWP As Range
Dim MBLWP As Range
Dim MBHAG As Range
Dim MBLAG As Range
Dim MBLOY As Range
Dim MBHOY As Range
Dim MBLTY As Range
Dim MBHTY As Range
Set RPT = Sheets("CODE").Range("A1")

With Sheets("Report")
Set MBHWP = .Range("A1129:E1175")
Set MBLWP = .Range("A1176:E1222")
Set MBHAG = .Range("A223:E1269")
Set MBLAG = .Range("A1270:E1316")
Set MBLOY = .Range("A1411:E1457")
Set MBHOY = .Range("A1458:E1504")
Set MBLTY = .Range("A1317:E1363")
Set MBHTY = .Range("A1364:E1410")
End With

If ComboBox2.Value = 40 Or ComboBox2.Value = 45 Then
If CheckBox1.Value = True Then
Set RPTA = Union(MBHWP, RPT)
Else
Set RPTA = RPT
If CheckBox2.Value = True Then
Set RPTB = Union(MBLWP, RPTA)
Else
Set RPTB = RPTA
If CheckBox3.Value = True Then
Set RPTC = Union(MBHAG, RPTB)
Else
Set RPTC = RPTB
If CheckBox4.Value = True Then
Set RPTD = Union(MBLAG, RPTC)
Else
Set RPTD = RPTC
If CheckBox5.Value = True Then
Set RPTE = Union(MBLOY, RPTD)
Else
Set RPTE = RPTD
If CheckBox6.Value = True Then
Set RPTF = Union(MBHOY, RPTE)
Else
Set RPTF = RPTE
If CheckBox12.Value = True Then
Set RPTG = Union(MBLTY, RPTF)
Else
Set RPTG = RPTF
If CheckBox7.Value = True Then
Set RPTH = Union(MBHTY, RPTG)
Else
Set RPTH = RPTG
End If
End If
End If
End If
End If
End If
End If
End If
End If