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!

 

[mvandeynze]

I wish to add clarification because There is a new problem to this code, however when I posted about it, it got flagged as a duplicate. But now I know that the problem with my code ISN'T how to select multiple ranges, it's how to get my Union and/or Range functions to work so here's some more information:

Here is my code: (bold is where the code stops)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Dim RPT As Range

 Set RPT = Sheets("CODE").Range("A1")
 With Sheets("Calculations")
 If ComboBox2.Value = 40 Or ComboBox2.Value = 45 Then
 If CheckBox1.Value = True Then
[B]Set RPT = Range(Range("A1129:E1175"), Range(RPT))[/B]
 Else
 Set RPT = RPT
 End If
 If CheckBox2.Value = True Then
 Set RPT = Range(.Range("A1176:E1222"), RPT)
 Else
 Set RPT = RPT
 End If
 If CheckBox3.Value = True Then
 Set RPT = Range(.Range("A223:E1269"), RPT)
 Else
 Set RPT = RPT
 End If
 If CheckBox4.Value = True Then
 Set RPT = Range(.Range("A1270:E1316"), RPT)
 Else
 Set RPT = RPT
 End If
 If CheckBox5.Value = True Then
 Set RPT = Range(.Range("A1411:E1457"), RPT)
 Else
 Set RPT = RPT
 End If
 If CheckBox6.Value = True Then
 Set RPT = Range(.Range("A1458:E1504"), RPT)
 Else
 Set RPT = RPT
 End If
 If CheckBox12.Value = True Then
 Set RPT = Range(.Range("A1317:E1363"), RPT)
 Else
 Set RPT = RPT
 End If
 If CheckBox7.Value = True Then
 Set RPT = Range(.Range("A1364:E1410"), RPT)
 Else
 Set RPT = RPT
 End If
 End If
 End With
 With ThisWorkbook
    .Sheets.Add(After:=.Sheets("Report")).Name = "Broker Profitability"
    End With

 Dim FName As Variant
    FName = Application.GetSaveAsFilename( _
        InitialFileName:="Broker Profitability.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
            Else

    End If

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
However I have tried multiple ways of setting the range some examples are below (they always start with Set RPT = )
Union(MBHWP, RPT)

Range(MBHWP, RPT)
'with MBWHP being set as the range listed in the bolded line where we get the error message. it is Dim MBWHP as range
Range(Range(MBWHP), RPT)

Range("MBHWP, RPT")

Union("MBHWP, RPT")

Range("A1129:E1175", RPT)

Union("A1129:E1175", RPT)

Range(Range("A1129:E1175"), RPT)

Union(Range(MBHWP), Range(RPT))

Range(Range(MBHWP), Range(RPT))


I've also probably tried more variations, but none come to mind right now.
I get the Method range of object global failed for when the function is range and
the method union of object global failed for when the function is union

I don't think it would be a problem with the rest of my code as it only ever stops at the first range/union line. I have also tried rearranging some of the IF statements in case that was the problem, however it has not fixed anything. I have searched tons of forums and I can't figure out why my code is not working.

I have also tried declaring more RPT variables so there were no repeats in any of the code (ex RPT, RPTA, RPTB, RPTC, RPTD, RPTE, RPTF, RPTG, RPTH, all dim as range) and then having
else
RPTA = RPT
(for example)

Is union even what I want to use or am I better off using range? I have read so many forums I'm confused as to whether or not union actually combines all the ranges together or if it only takes the common cells within the ranges as its range.

For reference all the cells and ranges are on the same sheet that is called "Reports" (I had read that the ranges not being on the same sheet is a problem for union, not that it changed my layout at all)

 

[Fluff]

You've said

For reference all the cells and ranges are on the same sheet that is called "Reports"

but your code has

Set RPT = Sheets("CODE").Range("A1")

Which is where the problem is.