VBA Named range in Array of worksheets

[Katy Jordan]

Hi, can a named range be used inside an array?

So

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

 

[Tuelor]

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]

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]

Something like..

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]

Something like..

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")

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]

Here's a way to do it without looping:

Dim vSheet As Variant
vSheet = Application.Transpose(Range("shtname"))

"shtname" is the name of your named range.

 

[Krishnakumar]

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

 

[Katy Jordan]

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]

Here's a way to do it without looping:

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]

Hi,

vSheet is itself a variant array.

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