Input box to enter worksheet name ??????

[Joe]

Hi,

I have an Excel file where I need to compare any two worksheets
selected by any given user:

Is the following logic possible?

1. An input box asks the user to enter the name of the worksheet, and
the input gets stored as a Variant - I've already done this.

eg: User inputs "Jan06"

2. Now I want to reference the worksheet "Jan06".

eg: I want to say something like
If Worksheet.("Jan06").Cells(2,1) = "CAR122" then ..........
etc etc etc

I tried the following but it didnt work:

Sub Autopopulate()
Dim a, b, c As Variant
Dim Sourcesheet As String

Sourcesheet = Application.InputBox("Enter the exact name of the
worksheet to transfer data FROM", Title:="Input Worksheet name",
Type:=2)

Set SS = Application.Worksheets.Sourcesheet

........................................................
.............................................................


Endsub()

Can someone help please?

Thanks in advance,

Joe.

PS: Is there something like an Activesheet function to work it around ?

 

[AMDRIT]

try this

Set SS = Application.Worksheets(Sourcesheet)

instead of

Set SS = Application.Worksheets.Sourcesheet


What error messages are you getting?

 

[Joe]

Superb! Brilliant! Splendid!!!!!!

thanks a lot, my friend!

It works perfectly!

And to answer your question, I had been getting error messages like :

"Compile error: method or data member not found"

Thanks again,

Joe.

 

[broro183]

Hi Joe,
I may be wrong (my excel is busy) but I think this should fix your
issue, esp the first line:

DIM SS AS WORKSHEET
Sourcesheet = Application.InputBox("Enter the exact name of the
worksheet to transfer data FROM (or type Activesheet)", Title:="Input
Worksheet name",
Type:=2)

select case Sourcesheet
case "Activesheet"
Set SS = activesheet
case else
Set SS = Application.Worksheets(Sourcesheet)
end select

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[AMDRIT]

I am glad that helped. Could I interest you into some advice though?


Excel is the Application and with in it, can be one or more workbooks.
Ideally one of those workbooks is yours, while the others are not. Instead
of referring to Application.Worksheets, be more specific and use
Activeworkbook.Worksheets.

When creating code in modules, always drill to your target from
activeworkbook. You do not have to be so diligent in the code panes for
ThisWorkbook, Sheet1, Sheet2 ... ect, as they already stem from the
activeworkbook.

By being specific, you learn to type really long usless code quickly with
lots of typos. No seriously, you allow your project to co-exist with other
projects harmoniously, and still get the expected results.

 

[Joe]

Thanks a lot, Rob.

I shall try that and let you know.

Joe.

 

[Joe]

Appreciate the advice, AMDRIT !

I'm in a learning mode, and every bit of information counts.

Thanks again,

Joe.