Using Array to Loop through specific sheets - need to add sheet name

**CRIMEDOG** · 11-09-2020, 04:50 PM

I am using an array to loop through specific sheets.

vSheets = Array(Sheet5, Sheet6, Sheet7, Sheet8, Sheet9, Sheet10, Sheet11, Sheet12, Sheet13, Sheet14, _
                    Sheet15, Sheet16, Sheet17, Sheet18, Sheet19, Sheet20, Sheet21, Sheet22, Sheet23, _
                    Sheet24, Sheet25, Sheet27, Sheet28, Sheet45)
 
   
 For x = LBound(vSheets) To UBound(vSheets)
  With vSheets(x)

I want to add the name of the sheet to range("I2") - I tried .range("I2").value=Worksheets.name

wks.name does not work

**Fluff13** · 11-09-2020, 04:58 PM

How about

.Range("I2").Value=vSheets(x).name

**CRIMEDOG** · 11-09-2020, 05:44 PM

It gets Object required error

**Fluff13** · 11-09-2020, 05:56 PM

Do all the sheets exist in your workbook?

**hrlngrv** · 11-09-2020, 06:19 PM

Good point.

Is there really any good reason for Option Explicit not to be the VBA default?

**hrlngrv** · 11-09-2020, 06:17 PM

The following works for me in a new workbook with 4 worksheets.

Sub foo()
  Dim bar As Variant, x As Variant, k As Long

  bar = Array(Sheet2, Sheet3)

  For Each x In bar
    x.Range("I2").Value = x.Name
  Next x

  For k = LBound(bar) To UBound(bar)
    bar(k).Range("I3").Value = bar(k).Name
  Next k

End Sub

**Fluff13** · 11-09-2020, 06:21 PM

Is there really any good reason for Option Explicit not to be the VBA default?

None that I can think of.

Using Array to Loop through specific sheets - need to add sheet name

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Using Array to Loop through specific sheets - need to add sheet name

Re: Using Array to Loop through specific sheets - need to add sheet name

Re: Using Array to Loop through specific sheets - need to add sheet name

Re: Using Array to Loop through specific sheets - need to add sheet name

Re: Using Array to Loop through specific sheets - need to add sheet name

Re: Using Array to Loop through specific sheets - need to add sheet name

Re: Using Array to Loop through specific sheets - need to add sheet name

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Loop through array of sheets every 30seconds until WB closed

[SOLVED] Store sheets into array via loop to copy to new book

[SOLVED] How to loop through two specific sheets

loop through specific sheets

[SOLVED] Help with: Range Array SelectCase Loop delete: Array All Sheet.Names

Loop thru an array of sheets

Macro to loop through sheets and print them out using an array

Bookmarks

Bookmarks

Posting Permissions