Can not Select Sheet
Hi all,
Here the part of my code runs well till the last moment of macro to select a specific Sheet "CALC". Would you please help me what am I doing wrong to achive Sheet "CALC" selection?
Thanks in advance for your kindest promts
Please Login or Register to view this content.
Try
Are you sure it's that line that is causing the error?Please Login or Register to view this content.
Have you stepped thought the code line by line when running it?
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
Is the sheet hidden?
Entia non sunt multiplicanda sine necessitate
Yeah, guessing is indeed an option they don't tell us everything
Sheet is not hidden. Sheets("CALC").Activate is not working as well.
I have not taking any error message just stopping in HELPER3 Sheet but not freezing excel
Last edited by zrs; 03-26-2019 at 12:42 PM.
Is it possible to attach the file? Make sure you remove sensitive data will make it easier to test maybe it has something to with the data in the sheets, also tell us exactly at which point the macro stops
Try this.
Please Login or Register to view this content.
If posting code please use code tags, see here.
Re: Can not Select Sheet
I tried all recommendations but no success. I attached the file but please mind I am an amateur just trying to automate my job so lots of code mistakes can be found. Except for returning the first sheet (CALC), I have more problems can be summarized as:
1- Range ("B2:B8") in "CALC" Sheet must be come empty for new entries not only opening of workbook but also selection of hotel from "B1".
2- Vba works very slow after hotel selection seems to need optimization which is excess of my skills.
This example file includes 3 hotels but will be between 100-150 in essential file. Thanks to all
Can you explain what macro I have to run?
There is no logic in your process and I see no indication what to run.
FillCell directly throws an error so I assume that I'doing something wrong.
No clear explanation, no help
Okay, found where to start. the macros stop in the macro FixSheet testing x=Nothing and x is nothing so exit sub.
Doesn't even range the place where CALC is to be selected
Dear Keebellah sorry for my less information. Macro starts from 1st Sheet (CALC) by hotel selection with an event procedure and ending with macro SubmitCalc() in 2nd module. Forget 3rd module is my helper while testing. Except for the speed of vba I have 2 problems in execution. 1- I can not go back to Sheet (CALC) in the end of last vba 2- I want to clear the previous inserted data in Range("B2:B8") of Sheet (CALC) after macro SubmitCalc() executed or in the opening of Workbook but always got error. Tnx
Okay, you should have said that at the beginning
Well there is no problem and nor error running the code now.
I found something strange and I think you did not notice it and that's why it didn't work, Ill place on sample of the macro and try to explain:
If you look the code line marked in red, you are invoking the next macro but you do that again at the endPlease Login or Register to view this content.
What then happens is that when all is complete the whole thing runs again and that 2 times so the last time the macro does not know where it is and the error comes, also there is nothing to calculate.
You did this in every macro with FormatSheet1 through FormatSheet12
the first sun will execute the macros 2 to 12 and aftre 12 the modules in the next batch. After that the control is returned to FormatSheet2 and the whole thing runs again run again and so 3- 12 once more and the final batch and the macro loses track.
You should 'read'the code and troubleshooting is one of the most time consuming parts.
I added two small macros, StopAllEvents and ResetAllEvents (see module3)
Hope it works, looks like to me now.
BTW you do not need Application.Run
Just the macro name is enough
FormatSheet2
Tnx again but seems being mistaken at my test. Possible failures in sheet (CALC):
1- Hotel name & all periods are gone (no copied data from HELPER3).
2- The normal starting row of prices is 18 at my copy but now goes to 21.
3- The non required areas are started to be inserted (in the bottom from Release days to last blank row) are affecting the validations between B5 & B7 as well.
You will have to check what the macro's do,.
I just checked why the entire process failed and came across the fact that you were running double routines.
I did not look or change the content of your macro's except for shortening them.. I suggest you make sure all the data is in place (copy of your file with the correct data) and make sure the modules from the file I sent you are in place and run again.
Other option is in your original file deactivate the lines I mentioned
Here's your own file, without having run the macro and the lines deactivated.
If the calculations are still wrong the it's for you to check your data
Like I mentioned, troubleshooting takes time.
Range D16 which is being checked seems to be empty
Keep your original file maybe you did something there to fill it in the second round, but I suggest you mark that line in every macro to break and then check where it stops.
Dear Keebellah,
I do not know what went wrong at my test last night but today I tested at office and everthing was fine. One more thanks for your kindest helps.
You're welcome. good luck with the file
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks