Arrays and Loops

[Kirk P.]

Thanks to Ron de Bruin, I've got code that copies data from a named range to
a "database" worksheet. I want to loop through all named ranges (which
represent a dynamic range in each worksheet) and copy them to the "database"
worksheet.

Here's what I've got:

Sub Consolidate()
Dim SourceRange As Range, DestRange As Range
Dim DestSheet As Worksheet, Lr As Long
Dim rName As Variant
Dim i As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Define the named range array
rName = Array("FBU1", "FBU2", "FBU3", "FBU4", "FBU5", "FBU6", "STAT1")

'Clear the Destination sheet
Sheets("Consolidated").Range("2:2", Selection.End(xlDown)).Delete
Shift:=xlUp

'fill in the Source Sheet and range
For i = LBound(rName) To UBound(rName)

Set SourceRange = Range(rName)

'Fill in the destination sheet and call the LastRow
'function to find the last row
Set DestSheet = Sheets("Consolidated")
Lr = LastRow(DestSheet)

'With the information from the LastRow function we can create a
'destination cell
Set DestRange = DestSheet.Range("A" & Lr + 1)

'Copy the source range and use PasteSpecial to paste in the destination
cell
SourceRange.Copy
DestRange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
Next i

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

When running this, I get a method Range of object _Global failed.

 

[Dave Peterson]

It might be as simple as:

Set SourceRange = Range(rName(i))

But I think I'd be more explicit. I'd specify the workbook where those names
are living:

Option Explicit
Sub Consolidate()
Dim SourceWkbk As Workbook
Dim SourceRange As Range
Dim DestRange As Range
Dim DestSheet As Worksheet
Dim Lr As Long
Dim rName As Variant
Dim i As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set SourceWkbk = ActiveWorkbook 'or something else???

'Define the named range array
rName = Array("FBU1", "FBU2", "FBU3", "FBU4", "FBU5", "FBU6", "STAT1")

'Clear the Destination sheet
With Worksheets("Consolidated")
.Rows("2:" & .Rows.Count).Delete
End With

'fill in the Source Sheet and range
For i = LBound(rName) To UBound(rName)
Set SourceRange = SourceWkbk.Names(rName(i)).RefersToRange

'Fill in the destination sheet and call the LastRow
'function to find the last row
Set DestSheet = Sheets("Consolidated")
Lr = LastRow(DestSheet)

'With the information from the LastRow function we can create a
'destination cell
Set DestRange = DestSheet.Range("A" & Lr + 1)

'Copy the source range and use PasteSpecial to paste in the dest cell
SourceRange.Copy
DestRange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
Next i

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

 

[Ron de Bruin]

Hi Kirk

Try it with this

Set SourceRange = Range(rName(i))

You can move this line outside the loop
Set DestSheet = Sheets("Consolidated")

Change your names
FBU1 is a cell in Excel 2007

Do all names exist in the array?

 

[Kirk P.]

Thanks Ron and Dave. I got it working with your help!