I wrote a macro that stores 2 values from one sheet then in another open worksheet it's supposed to search for one variable then enter the other. But when it gets to the part of the macro where it searches for the right cell it just stops. It doesn't run the search at all and I get no error message. Here's my code, can anyone tell me what I'm missing?
PS I use the sendkeys application because the names of the reports I use change everytime.
Thanks
Please Login or Register to view this content.
Last edited by imogul; 07-23-2013 at 02:23 PM.
Where are the worksheets/workbooks located exactly?
Why are you using SendKeys?
If posting code please use code tags, see here.
Like I said I'm using send keys because the names of the workbooks changes every time this report is run. The workbooks aren't saved anywhere at this point. They are just both open. I'm writing this for a novice excel user so I'm trying to make this as simple of them to use as I can.
Last edited by imogul; 07-23-2013 at 01:31 PM.
Don't use SendKeys, ever.
How are you opening the files?
Do you know there names?
I know this is not the most optimimal way to run a macro and if it were something that I was going to be running myself I would never do it in this manner. However just trust me that I am being forced to do it this way and there is no alternative in this one instance. The reports are sent via email and worked on in an open unsaved state until completed. I know that's not optimal either but it's what I have to account for. I don't and won't know the name of the workbooks because it changes every day to a number I can't predict.
Why not save them to a specific folder?
Then you could have code in a 3rd workbook that allows the user to navigate to and select the workbooks.
They could then be opened and the search/copy/paste can be done.
Using SendKeys to move between the workbooks isn't a good idea, for example how can you be sure you won't move to some other application?
Becuase the person who is going to be using this macro is an excel novice. She wouldn't understand that if I told her, and would refuse to do it even if she did. Also she is several paygrades above me so I can't exactly push her into doing it. The send keys I'm using are control+tab which only move to the next workbook, if it was alt+tab then that would be an issue but it should be fine since she has to be inside excel to run the macro anyway.
Why don't you refer to the workbooks using their Index?
eg Workbooks(1), Workbooks(2)
Becuase I don't know which one she will open first.
Where is this code locate?
Is there no way to tell the 2 workbooks apart?
I'm asking all this because I think the root of your problem could be SendKeys.
ok well what if I throw in something like this at the begining?
Then I can use the wbname variable to differentiate?Please Login or Register to view this content.
Yes, but it would be better to set a reference
You can then use that reference throughout the rest of the code whenever you need to refer to that worksheet.Please Login or Register to view this content.
PS You might want to give it a more descriptive name than ws, eg wsSource.
Ok I'll work it that way. Thanks for the help I think I figure it out from here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks