Method Range of object global failed

[mvandeynze]

https://www.experts-exchange.com/que...VBA-Excel.html this link is basically what I want to do but I don't think the solution applies here.
I want to select certain ranges to add to a new sheet as a report depending on which checkboxes are selected in the Userform.


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
Set RPT = Range(Range("A1129:E1175"), Range(RPT))
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)

I am on work day two of trying to figure out this error so any help is GREATLY appreciated.

Thank you.

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...ges-based-criteria-excel-vba.html#post5060563

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.