How do I change a variable name based on a sheet name

  • Thread starter Thread starter The Avenger
  • Start date Start date
T

The Avenger

For example, lets say I have a string variable named strSales, I want to
iterate through the sheets, so when I'm on sheet 1, it'll be strSales1, on
sheet 2 it'll be strSales2, etc.
 
Hi,

Maybe this

Sub nn()
myvar = "strSales"
For x = 1 To Worksheets.Count
myvar = myvar & x

'your code


myvar = "strSales"
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
I don't believe that will work. It, essentially, changes the myvar to the
value of myvar1, myvar2, etc. What I'm attempting to do is reference a
different variable, based on the sheet name. Sorry for not being clear
earlier.

If I'm on Sheets("George"), I want to reference variable strSalesGeorge, if
 
Hi,

It will work perfectly based upon your original question. This name includes
the sheet name

Sub nn()
myvar = "strSales"
For x = 1 To Worksheets.Count
myvar = myvar & Sheets(x).name

'your code
MsgBox myvar

myvar = "strSales"
Next
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Cool, ok, thank you

Mike H said:
Hi,

It will work perfectly based upon your original question. This name includes
the sheet name

Sub nn()
myvar = "strSales"
For x = 1 To Worksheets.Count
myvar = myvar & Sheets(x).name

'your code
MsgBox myvar

myvar = "strSales"
Next
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
Click to expand...
 
Back
Top