Link to home
Start Free TrialLog in
Avatar of jpwh
jpwh

asked on

VBA range definition error

Hi all,

The code below copies data from five named ranges in my workbook and pastes it to a worksheet. It works just fine when I run it from the sheet "Consolidated detail", but not when I run it from any other sheet or, most importantly, from the ActiveX button I've set up to run the macro. In these cases, I get run-time error 1004 on the line

Set Rng1 = Sheets("Consolidated detail").Range(Cells(1, "A"), Cells(LastRow, "AB"))

Is the problem here I'm not referencing the sheet correctly? Do I need to navigate to it before I set the range?

Any tips would be much appreciated.

Thanks,
Justin

***Full code follows***

Sub consolidate()
'This routine clears the "Consolidated detail" tab and then repopulates it with data from
'the "<acad>_MIP" detail tabs, which have the named ranges <acad>detail.
Dim Rng1 As Range   'Declare range object for "consolidateddetail" named range to be used in pivot table

Application.ScreenUpdating = False          'Turn off screen

Sheets("Consolidated detail").Cells.Clear   'Clear the tab
nNames = Array("rodetail", "kpcsdetail", "kdcadetail", "kpeadetail", "kwppdetail")
    For NameCounter = 0 To 4                'loop through the academy named ranges
        If NameCounter = 0 Then             'in first case, keep header row
            nrows = Range(nNames(NameCounter)).Rows.Count
            Range(nNames(NameCounter)).Copy
        Else                                'in all other cases, drop header row
            nrows = Range(nNames(NameCounter)).Rows.Count
            Range(nNames(NameCounter)).Resize(nrows - 1).Offset(1).Copy
        End If
        LastRow = Sheets("Consolidated detail").Cells(Rows.Count, 2).End(xlUp).Row
        Sheets("Consolidated detail").Range("A" & LastRow).PasteSpecial Paste:=xlPasteValues
    Next
   
LastRow = Sheets("Consolidated detail").Cells(Rows.Count, 2).End(xlUp).Row
Set Rng1 = Sheets("Consolidated detail").Range(Cells(1, "A"), Cells(LastRow, "AB"))
ActiveWorkbook.Names.Add Name:="consolidateddetail", RefersTo:=Rng1

Application.ScreenUpdating = True          'Turn screen back on

End Sub

Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

The problem is that you are qualifying the Range call with a worksheet, but not the Cells calls. Try:
With Sheets("Consolidated detail")
Set Rng1 = .Range("A1", .Cells(LastRow, "AB"))
End With

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of telyni19
telyni19
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jpwh
jpwh

ASKER

Good, fast advice as always on this forum. Thanks for your help!
Avatar of jpwh

ASKER

For completeness, attached is the code that worked. It needed both of your suggestions and a small change to your code, telyni19 -- moving the LastRow + 1 bit into the loop.

Thanks again,
Justin
Sub consolidate()
'This routine clears the "Consolidated detail" tab and then repopulates it with data from
'the "<acad>_MIP" detail tabs, which have the named ranges <acad>detail.
Dim Rng1 As Range   'Declare range object for "consolidateddetail" named range to be used in pivot table

Application.ScreenUpdating = False          'Turn off screen

Sheets("Consolidated detail").Cells.Clear   'Clear the tab
nNames = Array("ro", "kpcs", "kdca", "kpea", "kwpp")
    For NameCounter = 0 To 4                'loop through the academy named sheets & ranges
        If NameCounter = 0 Then             'in first case, keep header row
            nrows = Sheets(nNames(NameCounter) & "_MIP").Range(nNames(NameCounter) & "detail").Rows.Count
            Sheets(nNames(NameCounter) & "_MIP").Range(nNames(NameCounter) & "detail").Copy
        Else                                'in all other cases, drop header row
            nrows = Sheets(nNames(NameCounter) & "_MIP").Range(nNames(NameCounter) & "detail").Rows.Count
            Sheets(nNames(NameCounter) & "_MIP").Range(nNames(NameCounter) & "detail").Resize(nrows - 1).Offset(1).Copy
        End If
        LastRow = Sheets("Consolidated detail").Cells(Rows.Count, 2).End(xlUp).Row
        If LastRow > 1 Then LastRow = LastRow + 1
        Sheets("Consolidated detail").Range("A" & LastRow).PasteSpecial Paste:=xlPasteValues
    Next
   
LastRow = Sheets("Consolidated detail").Cells(Rows.Count, 2).End(xlUp).Row
With Sheets("Consolidated detail")
    Set Rng1 = .Range("A1", .Cells(LastRow, "AB"))
End With

ActiveWorkbook.Names.Add Name:="consolidateddetail", RefersTo:=Rng1

Application.ScreenUpdating = True          'Turn screen back on

End Sub

Open in new window

Oh, sorry, yes. I had it correct in the code I tested and then copied it in piecemeal and put it in the wrong place when I copied it in again. The LastRow tweak should be inside the loop. And rorya was right in what he said too.
Avatar of jpwh

ASKER

One last thought/question here: If the <acad>detail named ranges are scoped to the workbook, do I actually have to specify the <acad>_MIP sheets in the loop? Want to make sure I understand how Excel is working.

Thanks,
Justin
It didn't work for me when I didn't specify the sheet name. Note that you can have two named ranges with the same name if they are on different sheets, so the scope of a named range is a particular sheet, not the whole workbook.