?
Solved

VBA range definition error

Posted on 2011-02-15
7
Medium Priority
?
461 Views
Last Modified: 2012-05-11
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
Comment
Question by:jpwh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34900665
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
 
LVL 12

Accepted Solution

by:
telyni19 earned 200 total points
ID: 34900804
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
 

Author Comment

by:jpwh
ID: 34900938
Good, fast advice as always on this forum. Thanks for your help!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jpwh
ID: 34901500
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
 
LVL 12

Expert Comment

by:telyni19
ID: 34901542
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
 

Author Comment

by:jpwh
ID: 34903246
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
 
LVL 12

Expert Comment

by:telyni19
ID: 34903412
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question