Originally Posted by
delaing
....
Okay . . . that is a lot, but doing my darndest to do better so you won't have to invest so much time critiquing my posting and I can improve.........
..
. As I said, usually you do not need to go so far: the pros are very good at guessing what you want: -
Stinky Nuts has been “On the Ball” from the outset, The solution I give now is not really anything other than his, All credit goes to him, I have learnt a lot from following his solutions..
. I expect he would hit on the solution to your latest problem from just your description...
. But your very clear last post #16 made it so clear that it both helped me to use / learn from Stinky nuts codes, and further I think I have a final working solution for you: ( In fact I think your last problem is very minor, and I actually learnt a lot from following through you code!!, (although I do not understand it all. In particular what Set StartSheet = Workbooks("start..... is doing tacked on inside The code bit containing the Stinky code is puzzelling me.... ?????)
...................
. Anyways.. Your problem came as you are referring to the Main Page in the original File through the ws which is defined in the large code at the start of the code as the main FileCopy sheet in the original File, when the Main file was the Active file.. ( The reason why it worked when Call - ing it is simple: The Call will start that code when the Copy file is active, and so the ws will refer to that Currently Active File Main Page which is that of the Copy file. )
So very simply in your last given Large code change this
To this
. The stinky code then applies to the correct sheet, which is that active, that is to say the one you see.
. The code then works by me.
.
. At the end of the day you just need to be very careful and explicit in your defining of things like sheets. Otherwise problems such as your last are very common. Most profies will tell you to avoid using and relying on Active things as much as possible... I would have probably have saved the Copy file straight after you had created it through the...
Sheets("Main Page").Select
Sheets("Main Page").Copy
....bit and defined the Main Page in that File at that point,
Or at least to minimise confusion modify your large code thus:
Alan
P.s.
This is an ( untypically ) good Microsoft link for the .Find Method
https://msdn.microsoft.com/de-de/lib.../Ff839746.aspx
and P.P.s. Here is another Smelly Code, based on what I learnt from this Thread. It has a few ways to find the “ACCT“ column ( But stinky’s second is better as it is not relying on an error handler, which is always the more professional approach
Bookmarks