Solved

VBA range definition error

Posted on 2011-02-15
7
436 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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 will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

708 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

16 Experts available now in Live!

Get 1:1 Help Now