Solved

VBA range definition error

Posted on 2011-02-15
7
438 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
  • 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 50 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now