CODEWITHSUNDEEP
vbaexcel
IronKirby
IronKirby

Reputation: 708

Q: Moving between Excel Tabs with Indexes

I'm attempting to loop through a series of Excel Tabs (Labelled Wk 1 to Wk 52) and extract the balance Value stored in Week 1 (K40) and have this value transferred to Week 2 (D10). (Week 3 would then be compared against Week 2 etc.) Please refer to the attached image. Do excel tabs have indexes you can refer towards? The reason for this is my pseudo-code below:

  For ws in Worksheets  'Cycle through all worksheets
      IF RIGHT(ws.name, 2) < RIGHT(ws.name + 1,2) THEN 
'As the worksheets are labelled Wk 1 Wk 2 etc. RIGHT 2 will only take the number values, not Wk
         Select ws.name
         tmpVar = Range("K40")
         Select ws.name + 1
         D10.Value = tmpVar
      END IF
  Next ws

If anyone could let me know if excel has tab indexes I can refer to that would be great. I looked through the Microsoft website (https://msdn.microsoft.com/en-us/library/office/aa221564(v=office.11).aspx) but what I need is to be able to move to the next tab (ws.name + 1) as that's what I don't know how to get. Apologies for the novice question - I'm still familiarizing myself with VBA.

Balance Sheet Sample

Upvotes: 0

Views: 73

Answers (2)

user4039065
user4039065

Reputation:

Yes, you can refer to a worksheet by its index number.

dim w as long
for w = 1 to worksheets.count - 1
    with worksheets(w)
        If IsNumeric(Right(.Name, 2)) And IsNumeric(Right(Worksheets(w + 1).Name, 2)) Then
            if CLng(right(.name, 2)) = CLng(right(worksheets(w + 1).name, 2)) - 1 then
                worksheets(w + 1).cells(10, 4) = .cells(40, 11).value2
            end if
        end if
    end with
next w

Upvotes: 2

chris neilsen
chris neilsen

Reputation: 53126

The Worksheets collection does have an index property, and you can refer to worksheets by this index (as Jeeped illustrates)

However, the index of a sheet is its current position as displayed in Excel (count the tabs from the left, including counting hidden sheets). This position can be changed by the user, so is risky to use in this case.

Better to make your ws.name + 1 actually work

eg

Dim ThisWeekNum as long
Dim wsThisWeek as Worksheet
Dim wsNextWeek as Worksheet

ThisWeekNum = 1
Set wsThisWeek = Worksheets("Wk " & ThisWeekNum)
Set wsNextWeek = Worksheets("Wk " &  Val(Right$(wsThisWeek.Name, 2)) + 1)

Note, the user can also rename worksheets, so this has its risks too

Upvotes: 0

Related Questions