• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

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

0
jpwh
Asked:
jpwh
  • 3
  • 3
1 Solution
 
Rory ArchibaldCommented:
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

0
 
telyni19Commented:
I tried testing your code, and I ran into two issues, actually.

First, there are unspecified Range statements in the If statement that also generated the 1004 error for me, even when run from a "Consolidated detail" sheet. I think you probably want to use the <acad> abbreviations as your variables and reference both the sheets and the named ranges using that, so the array and first part of the If statement would look like this:

nNames = Array("ro", "kpcs", "kdca", "kpea", "kwpp")
    For NameCounter = 0 To 4                'loop through the academy named 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

Second, it seems to be overwriting the last row of the previous set of data when it copies the next set. To fix this, add this line in after LastRow is calculated:
If LastRow > 1 Then LastRow = LastRow + 1

The full code is attached.
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 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
        Sheets("Consolidated detail").Range("A" & LastRow).PasteSpecial Paste:=xlPasteValues
    Next
   
LastRow = Sheets("Consolidated detail").Cells(Rows.Count, 2).End(xlUp).Row
If LastRow > 1 Then LastRow = LastRow + 1
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

Open in new window

0
 
jpwhAuthor Commented:
Good, fast advice as always on this forum. Thanks for your help!
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
jpwhAuthor Commented:
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

0
 
telyni19Commented:
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.
0
 
jpwhAuthor Commented:
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
0
 
telyni19Commented:
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.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now