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

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
>

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" <[email protected]> wrote in message
news:[email protected]...
>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
>

"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
> >