Runtime Error 1004: Method Range of Object _Global Failed

[one3nine0]

Hi All

I have a file with a macro that works in the original file, but when I copied the file to make my own changes, the macro no longer works in the copied file.

I get a Error code 1004 Method Range of Object _Global Failed on the highlighted line of code, which is trying to set rng = Range (cell.Value)

The range is a named range "stmtWorksheetname" which references a selected group of cells in a different sheet.

Any idea of why it is erroring out?

Sub CreateRebateReports()
    Dim wbA As Workbook
    Dim wbB As Workbook
    Dim ws As Worksheet
    Dim nName As Name
    Dim cell As Range
    Dim rptData As Range
    Dim rng As Range
    Dim strBody As String
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim strName As String
    Dim firstrow As Long
    Dim lastrow As Long
        
    Application.EnableEvents = False
        
        
    'On Error Resume Next
    Set wbA = ActiveWorkbook
    
    'Delete all files in the directory that the reports are saved to.
    'Kill "C:\Users\lain\Documents\Rebate Statements*.*"


    For Each cell In Range("rngEmailTable").Cells
         
        'Copy the current range name to the Sales Department Statement
        [COLOR=#FF0000]Set rng = Range(cell.Value)[/COLOR]
        
        Worksheets("Rebate Statement").Range("A1", "A1000").EntireRow.Clear
                
        rng.Copy
        Worksheets("Rebate Statement").Range("A1").PasteSpecial Paste:=xlPasteValues
        Worksheets("Rebate Statement").Range("A1").PasteSpecial Paste:=xlPasteFormats
        Worksheets("Rebate Statement").Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
        
        Worksheets("Rebate Statement").Range("A1:A100").EntireRow.AutoFit
        
        'Copy relevant pages from main workbook to a new workbook
        Worksheets(sht01SalesDeptStatement.Name).Copy
        Set wbB = ActiveWorkbook
                
        'MsgBox wbB.Name
        
        wbB.Activate
        ActiveWindow.Zoom = 80
                                
        'Save File with Name
        TempFilePath = "C:\Users\lain\Documents\Rebate Statements"
        TempFileName = cell.Offset(0, 4).Value
        FileExtStr = ".xlsx"
        
        'MsgBox TempFileName
        
        
        Application.DisplayAlerts = False
        wbB.SaveAs TempFilePath & TempFileName & FileExtStr


        wbB.Close
                
    Next
               
    MsgBox "All Rebate Reports Have Been Created!"
       
End Sub

 

[JLGWhiz]

[COLOR=#ff0000]Set rng = Range(cell.Value)[/COLOR]

Can you post and example of the cell.Value?

 

[one3nine0]

So the range "rngEmailTable" refers to this named range "=OFFSET('Email Table'!$A$2,0,0,COUNTA('Email Table'!$A:$A)-1,1)" which is supposed to select all the named ranges in the column of my sheet.

The current named range in that sheet is "stmtWorksheetname" which refers to "='Worksheet Name'!$A$60:$J$81"

 

[one3nine0]

Figured it out, the named ranges were scoped to the worksheet and not the workbook. Need to recreate the named ranges and use the correct scope

 

[JLGWhiz]

So the range "rngEmailTable" refers to this named range "=OFFSET('Email Table'!$A$2,0,0,COUNTA('Email Table'!$A:$A)-1,1)" which is supposed to select all the named ranges in the column of my sheet.

The current named range in that sheet is "stmtWorksheetname" which refers to "='Worksheet Name'!$A$60:$J$81"

You declared rng As Range. When you use the Set statement to initialize the variable, it must return a range object. In the loop you have created, if any of the cells which the 'cell' does not return a range address, eg. "$A$1", the you will get an error. If the cell is returning a range object, which it appears to be doing then you would not use the keyword Range in the statement but just use.

Set rng = cell.Value

Stated differently, if the cell returns an address, then Range(cell.Value) should yield a range object, but if the cell returns a range object then do not use Range as a qualifier.