VBA Named range in Array of worksheets

Katy Jordan

Well-known Member
Joined
Jun 28, 2008
Messages
596
Hi, can a named range be used inside an array?

So

Worksheets(Wary(a))Array("NamedRange")
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Tuelor

Board Regular
Joined
Mar 19, 2011
Messages
101
You could loop through each cell in the named range adding it to the array - not sure if you can add it all in one though.

D
 

Katy Jordan

Well-known Member
Joined
Jun 28, 2008
Messages
596
In sheet3 i have a list of worksheets which i have named "rngsheets", the sheets in this range are "Test", "Test2"

I dont want to hardcode the sheets in Array(I want to input named range)

Is this possible?
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Something like..

Code:
Dim wksAry, i As Long

wksAry = Range("NamedRange")

For i = 1 To UBound(wksAry, 1)
    With Worksheets(CStr(wksAry(i, 1)))
        'do something
    End With
Next
 

Katy Jordan

Well-known Member
Joined
Jun 28, 2008
Messages
596

ADVERTISEMENT

Something like..

Code:
Dim wksAry, i As Long

wksAry = Range("NamedRange")

For i = 1 To UBound(wksAry, 1)
    With Worksheets(CStr(wksAry(i, 1)))
        'do something
    End With
Next

How would i use your method with the below?

WSary = Array("Sheet1", "Sheet2")


Code:
With ShtRaw
  LR = .Cells(Rows.Count, 1).End(xlUp).Row
  .AutoFilterMode = False
  With .Range("A5:O" & LR)
    For a = LBound(WSary) To UBound(WSary)
      .AutoFilter Field:=2, Criteria1:=WSary(a)
      NR = Worksheets(WSary(a)).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      'Copy Data to destination worksheets
      On Error Resume Next
      .Columns("A:C").Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy
      Worksheets(WSary(a)).Range("A" & NR).PasteSpecial Paste:=xlPasteValues
      .Columns("F:O").Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy
      Worksheets(WSary(a)).Range("D" & NR).PasteSpecial Paste:=xlPasteValues
      NR = Worksheets(WSary(a)).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      
      'Apply Formatting
      With Worksheets(WSary(a)).Range("A6:M" & NR & ",Q6:R" & NR)
        .Borders.LineStyle = xlNone
        .Borders.LineStyle = xlContinuous
        .Interior.ColorIndex = 0
        .Borders.Weight = xlHairline
       End With
</pre>
 

sous2817

Well-known Member
Joined
Feb 22, 2008
Messages
2,276
Here's a way to do it without looping:
Code:
Dim vSheet As Variant
vSheet = Application.Transpose(Range("shtname"))


"shtname" is the name of your named range.
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615

ADVERTISEMENT

Do you get any error ? It seems OK to me.
 

Katy Jordan

Well-known Member
Joined
Jun 28, 2008
Messages
596
Do you get any error ? It seems OK to me.

How do i reflect your method with this, wherever i have (Wsary(a)) what do i replace it?


For a = LBound(WSary) To UBound(WSary)

NR = Worksheets(WSary(a)).Range("A" & Rows.Count).End(xlUp).Offset(1).Row

</pre>
 

Katy Jordan

Well-known Member
Joined
Jun 28, 2008
Messages
596
Here's a way to do it without looping:
Code:
Dim vSheet As Variant
vSheet = Application.Transpose(Range("shtname"))
"shtname" is the name of your named range.

So do i do this

vSheet = Application.Transpose(Range("shtname"))
WSary = Array(vSheet)
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

vSheet is itself a variant array.

BTW, can you tell me briefly what are you trying to do ?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,661
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top