Finding sheet that matches a cell value

[oakman]

Please forgive my insistence, but I am so close to achieving my goal. Know that I truly value any and every piece of advice given to me in this Forum.
Please take look at the code below, it has come from helpful postings to a previous thread of mine. However, I don’t think that I was clear enough in expressing what my problem was, so I think that I ended up causing some confusion. In an effort to more clearly express my problem, I have worked on the code – with as limited knowledge as I possess – to perhaps communicate my intention. I know for a fact that in the code below there are 2 problematic lines – and herein lies the root of my problem. The first line is: “Dim Day As String” In Debugger speak, I am told that this cannot be done along with “Set sh = Worksheets(Day.Value)”. However, in normal speak, I am trying to copy data from one sheet to another. When pasting the data on the second sheet, I am trying to make the code find the appropriate sheet that matches a Day value in Cell I5. Getting the code to execute this one action is proving to be particularly difficult. I am thankful for any suggestion on how to get the code to work correctly.

Please Login or Register  to view this content.

[StephenR]

As it's a string try just losing the value:

Please Login or Register  to view this content.

[oakman]

Thank you for the suggestion. I have tried it. The code does not give me an error, but at the same time, it does not find the sheet with the Day value in Cell I5 either.

[chergh]

What is actually in cell I5?

It may be you need:

Please Login or Register  to view this content.

[royUK]

This is part of your earlier question here

http://www.excelforum.com/showthread.php?t=646475

It should be continued in that Thread. I've already given you code that should work according to what you said there. What is not working with this code?

Please Login or Register  to view this content.

[oakman]

Chergh, I tried your suggestion, but no luck. The value in Cell I5 is the result of the following formula:"=IF(VALUE(H5)>10,TEXT(VLOOKUP(F5,A6:B36,2,FALSE),"dd"),TEXT(VLOOKUP(F5,A6:B36,2,FALSE),"d"))"
Do you guys think that the code does not know how to interpret the number in Cell I5 b/c it is a Formula result?

[oakman]

RoyUK, you are correct. The question in this thread pertains to the one that you referring to. I do not want to see ungracious for all the help that you have given me. I just did not know how to approach bringing back the question up. I did not want to seem burdensome, so what I figured I would do is to try to work on the code as much as I could to make my question clearer. I have tried the code that you have provided and I get a run-time error '438' with the following line of code highlighted in yellow:
wb.Range("K8:K31").Copy ThisFile.Worksheets(Day).Range("C7:C30")
The message in the error is as follows:
Object doesn't support this property or method.

Again, I do not want to seem burdensome with my question. My intention was to do as much work on my own. Thank you for any help.

[chergh]

Try this:

Please Login or Register  to view this content.

[royUK]

RoyUK, you are correct. The question in this thread pertains to the one that you referring to. I do not want to see ungracious for all the help that you have given me. I just did not know how to approach bringing back the question up. I did not want to seem burdensome, so what I figured I would do is to try to work on the code as much as I could to make my question clearer. I have tried the code that you have provided and I get a run-time error '438' with the following line of code highlighted in yellow:
wb.Range("K8:K31").Copy ThisFile.Worksheets(Day).Range("C7:C30")
The message in the error is as follows:
Object doesn't support this property or method.

Again, I do not want to seem burdensome with my question. My intention was to do as much work on my own. Thank you for any help.

What is the value returned in H5 & do you have a sheet with that name in ThisFile?

[BigBas]

I don't know if there is a difference in versions or something, but I am running Excel 2000 at work. I set up a dummy file using the "ThisFile" code and also pulled up an error.

When I changed ThisFile to ThisWorkbook, it worked for me. Consider making that change.

[oakman]

That was it!
That change to the code made it work. What was missing? Please tell me if you have the time...I would like to be able to understand so that in the future I can be less of a bother.
Again, I did not mean to seem inconisderate or ungracious for any help provided. It is just that this Forum is my single access to knowledge. I appreciate all the advice that has been provided! Truly!

[oakman]

The change that I was talking about was the one suggested by Chergh. BigBas, I will try your suggestion as well since I am trying to learn as much as I can.
Thank you

[BigBas]

You can disregard my suggestion. I missed the code line where ThisFile was actually an established variable.

[oakman]

RoyUK,
the value returned in the Cell H5 in the example that I provide is the number 5
This corresponds to the Day that I am trying to update.

In "ThisFile", I do have a sheet named "5"(w/o the quotes).
Does this make sense?
Does this help in terms of trying to figure out why the '438' Run-time error?

Thank you

[chergh]

What you have originially was

Please Login or Register  to view this content.

Which is equivalent to:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Is equivalent to

Please Login or Register  to view this content.

Always explicitly tell excel which workbook and worksheet you want it do things on.

[royUK]

The code does specify the workbook & the worksheet. add a check to see what is happening

Please Login or Register  to view this content.

[royUK]

Try this

Please Login or Register  to view this content.

It will copy from the active sheet in the source workbook.

[oakman]

RoyUK,
I have tried the code that you kindly provided and it worked very well. I saw that you made a change in the "Set wb" line of code. That got rid of the '438' Run-time error.
Please know that I am grateful and truly appreciative of your continued help!