Method 'Range' of object '_Global' failed

Question

I am trying to loop through the items in three drop down lists, with the named ranges A, B, and C. The Summary output changes based on the items in the drop down list chosen. I would like to copy each summary output (a table of 19 rows by 15 columns) and paste them into a new Sheet (Sheet 3).

For example, if I had 3 items in List A, 2 items in List B, and 2 items in List C, I would have a total of 12 outputs that I need (3*2*2) pasted into Sheet 3.

When I run this code, I get the following error:

Method 'Range' of object '_Global' failed

I would appreciate any help possible! I'm a newbie to VBA programming, so I'm sure there's a more efficient way to do this...

Thanks!

Sub SummarizeData()
    '
    ' SummarizeData Macro
    '

    Dim rngCeded As Range
    Dim rngTF As Range
    Dim rngFX As Range
    Dim LOS As Integer

    Set rngCeded = Range("A")
    Set rngTF = Range("B")
    Set rngFX = Range("C")

    LOS = 19
    For n = 1 To 12
        For Each i In Range("A")
            For Each j In Range("B")
                For Each k In Range("C")
                    Sheets("Summary").Range("SummaryData").Copy
                    Sheets("Sheet3").Range("E5").Offset(i - 1, 0).PasteSpecial Paste:=xlPasteValues
                Next k
            Next j
        Next i
    n = n + LOS
    Next n
    End Sub

Answer 1

Since you do not specify where the Range comes from (Set rng = Range("...")), the outcome of the code depends on the context you call the method.

It is better to call the Range method of a specific object. In your case, that might be a Worksheet-object, similar to this:

' ...
Dim MySheet As Excel.Worksheet
Set MySheet = ActiveWorkbook.Sheets("MySheet")
Set rngCeded = MySheet.Range("A") 
' ...

Answer 2

You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.

Reference

Sub SummarizeData()
    '
    ' SummarizeData Macro
    '

    Dim rngCeded As Range
    Dim rngTF As Range
    Dim rngFX As Range
    Dim LOS As Integer

    Set rngCeded = Range("A")
    Set rngTF = Range("B")
    Set rngFX = Range("D")

    LOS = 19
    For n = 1 To 12
        For Each i In Range("A")
            For Each j In Range("B")
                For Each k In Range("D")
                    Sheets("Summary").Range("SummaryData").Copy
                    Sheets("Sheet3").Range("E5").Offset(i - 1, 0).PasteSpecial Paste:=xlPasteValues
                Next k
            Next j
        Next i
    n = n + LOS
    Next n
    End Sub