CODEWITHSUNDEEP
vbaexcel
AmMik
AmMik

Reputation: 1

Using variable sheetname in VBA

I'm a novice at VBA (just starting to learn). In my userform I have a combobox with values from 1 to 12(not string) representing the months. I want the user to pick a month and based on that, the multiple listboxes and labels I have placed should get filled by the relevant values in one of the 12 sheets representing each month. as I am a novice I have a lot of problems here but for starters the following lines do not seem to work on userform_initiate()

For j = 0 To 1
    arr_trh(0, j) = Sheets("Sheet6").Cells(4, j + 1)
Next j

I can get it to work for a single sheet by using

arr_trh(0, j) = Sheet6.Cells(4, j + 1)

However, what I'm trying to do later on is to create a string and somehow concatenate "Sheet" and combobox value to pass on to Sheets() function.

Any help would be appreciated Thanks

Upvotes: 0

Views: 60

Answers (2)

JohnyL
JohnyL

Reputation: 7142

I guess, my this answer will help you to figure out how to refer to sheets. Also, it tells about caveats of Index property.

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Rather than referring to the Sheet object like:

v = Sheet1.Range("A1")

use:

v = Sheets(1).Range("A1")

which you can index like:

v = Sheets(i).Range("A1")

where i is a variable.

Upvotes: 1

Related Questions