how to reference a worksheet with a variable vs a name
 

I have 3 worksheets that I fill with Data depending if the country is US,
Canada or Not Defined. What I want to do is reference the worksheet with a
variable such as

if country = US then n = 1
else if country = Canada then n = 2
else n = 3
endif

This worksheet(n) = the data

I don't know if this can be done. I get an error when I try it. If I can
do this then I don't have to write a section for each of the different
worksheets.

Thanks In advance

how to reference a worksheet with a variable vs a name
 

here is something i came up with
Option Explicit
Dim country As String
Dim n As Long
Sub test()
country = "US"
If country = "US" Then
n = 2
Else
n = 3
End If
Sheets(n).Activate
End Sub

--


Gary


"ibbm" wrote in message
...
I have 3 worksheets that I fill with Data depending if the country is US,
Canada or Not Defined. What I want to do is reference the worksheet with a
variable such as

if country = US then n = 1
else if country = Canada then n = 2
else n = 3
endif

This worksheet(n) = the data

I don't know if this can be done. I get an error when I try it. If I can
do this then I don't have to write a section for each of the different
worksheets.

Thanks In advance

how to reference a worksheet with a variable vs a name
 

maybe this will help:

Dim ShtName as String
ShtName = "Sheet" & str(n)

reference it like this:
ThisWorkbook.Worksheets(ShtName)

Hope this helps
-Steve


"ibbm" wrote:

I have 3 worksheets that I fill with Data depending if the country is US,
Canada or Not Defined. What I want to do is reference the worksheet with a
variable such as

if country = US then n = 1
else if country = Canada then n = 2
else n = 3
endif

This worksheet(n) = the data

I don't know if this can be done. I get an error when I try it. If I can
do this then I don't have to write a section for each of the different
worksheets.

Thanks In advance

how to reference a worksheet with a variable vs a name
 

"Patch61" wrote:

maybe this will help:

Dim ShtName as String
ShtName = "Sheet" & str(n)

reference it like this:

Hope this helps
-Steve

In theory I should be able to go
ShtName = "Canada"
ThisWorkbook.Worksheets(ShtName)
ThisWorkbook.Sheets(ns).Range(Cells(2, 3), Cells(2, 13)).Select
Range(Cells(2, 3), Cells(2, 13)).Value = "Sales in Units"

But I get an error message 1004 Application defined or object error
"ibbm" wrote:



I have 3 worksheets that I fill with Data depending if the country is US,
Canada or Not Defined. What I want to do is reference the worksheet with a
variable such as

if country = US then n = 1
else if country = Canada then n = 2
else n = 3
endif

This worksheet(n) = the data

I don't know if this can be done. I get an error when I try it. If I can
do this then I don't have to write a section for each of the different
worksheets.

Thanks In advance