variable sheet name

[Gisela]

Is there a way to refer to use a variable as the sheet name in a macro? I
getting run time error 424 here:

Set wsFrom = Sheets("Menu").Cells(w, 19).Value

For example, let's say that in columns S, I've got the names of sheets that
I want to refer to...

Column S
Sheet7
Sheet8
Sheet9
Sheet10

Dim wsFrom As Worksheet

I want to move the cell value to:

Set wsFrom = Sheets("Menu").Cells(w, 19).Value

where: w = 1 <---row 1 of column S; it increases by one until the product
changes

The purpose is to move certain data to a different worksheets (some code is
included)
Do Until IsEmpty(Sheets(wsFrom).Cells(x, 2))
'This will move the value of each column within each row to Data
worksheet
Do Until y = 19
Sheets("Data").Cells(x, y).Value = Sheets(wsFrom).Cells(x, y).Value
'increase the value of y by 1 to act on the next column
y = y + 1
Loop
'increase the value of x by 1 to act on the next row on Data worksheet
x = x + 1
'sets the value of y to 2 to act on the first column to move next row data
y = 2
Loop

 

[KC]

Try it this way

Set wsFrom = Sheets("Menu")
wsFrom.Cells(w,19).Value

hope this helps?

regards,
-kc
click YES if this helps.

 

[Gisela]

The Menu sheet cell (w,19) has the name of the worksheet to be used later.
How can I do that? Is it possible?

 

[Chip Pearson]

Assume your sheet names are in column S of Sheet1, then you can use
code like the following:

Dim R As Range
Dim WS As Worksheet
Set R = Worksheets("Sheet1").Range("S1")
Do Until R.Text = vbNullString
Set WS = Worksheets(R.Text)
' do something with WS
Debug.Print WS.Name
Set R = R(2, 1)
Loop


Here, R is initialized to S1 on Sheet1. The code then loops, and sets
the WS variable to the Worksheet whose name is in the cells on column
S. Your code can then do whatever it needs to do with WS, and then the
loop continues reading column S on Sheet1 to get the subsequent sheet
names. It terminates when an empty cell is encountered in column S of
Sheet1.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[KC]

Set wsFrom = Sheets(Sheets("Menu").Cells(w, 19).Value)
wsFrom.Cells(x,y).value.......

would this help?
-kc

 

[Gisela]

Chip:

The row number where the worksheet name is, varies depending on the amount
of products. That’s why I need to have a variable to indicate which row needs
to be accessed. In this case I'm using w to determine the row number. Is it
possible to do that?

Set wsFrom = Sheets("Menu").Cells(w, 19).Value

 

[Gisela]

I found the problem. I was defining wsfrom as a worksheet instead of a
String. My code is working.

Thanks anyway!!