I would greatly appreciate any assistance with this...
I'm new at working with macros and I am trying to record a
macro with which I am having no luck. I'm starting to
wonder if it can be done.
I have, in one workbook, a sheet where a user will type
the first 3 letters of any month of the year into cell A1.
Ideally I would like to record a macro which will then
basically 'look at' which 3 letters have been typed into
A1 and will go and find the Worksheet (in the same
Workbook) whose name matches.
So you type "Jan" into A1 of Sheet1
You then run the macro and it looks at the names (on the
tabs) of the other sheets in the workbook until it finds
the Worksheet with "Jan" on the tab.
Then I want to copy the contents of that sheet and paste
it elsewhere but I'm okay with that bit. I just need to
know how - if it's possible - to have a macro look at the
value of a particular cell then look for the sheet whose
name is equal to that value.
MANY MANY thanks
mercedes

Hi
try something like the following:
sub foo()
dim source_wks as worksheet
dim get_wks as worksheet
Dik wks_name
set get_wks = activesheet
wks_name=get_wks.range("A1").value
if wks_name="" then exit sub

on error resume next
set source_wks=worksheets(wks_name)
on error goto 0
if source_wks is nothing then
msgbox "Sheet does not exist"
exit sub
'your other code
end sub


-----Original Message-----
I would greatly appreciate any assistance with this...
I'm new at working with macros and I am trying to record
a
macro with which I am having no luck. I'm starting to
wonder if it can be done.
I have, in one workbook, a sheet where a user will type
the first 3 letters of any month of the year into cell A1.
Ideally I would like to record a macro which will then
basically 'look at' which 3 letters have been typed into
A1 and will go and find the Worksheet (in the same
Workbook) whose name matches.
So you type "Jan" into A1 of Sheet1
You then run the macro and it looks at the names (on the
tabs) of the other sheets in the workbook until it finds
the Worksheet with "Jan" on the tab.
Then I want to copy the contents of that sheet and paste
it elsewhere but I'm okay with that bit. I just need to
know how - if it's possible - to have a macro look at the
value of a particular cell then look for the sheet whose
name is equal to that value.
MANY MANY thanks
mercedes
.