Solved

call different sheets

Posted on 2014-11-06
64
51 Views
Last Modified: 2014-11-19
I am using the below Macro [which is only the second part] provided by Gowflow which is absolutely brilliant but would like to make a change to it which will make it even more brilliant

The [first part of the] Macro takes data from my file and copies this info into another file [Sign-Off-Log.xlsx] on Sheet1. This part of the Macro then apends to the initial data.

However, my file has 3 stages so I have put the code in Sheets1, 2 & 3. The code in Sheet 1 is fine and it does exactly what I need. For sheets 2 I need the Macro to open [Sign-Off-Log.xlsx] in Sheet2 and for Sheet3 I need the code to open the file [Sign-Off-Log.xlsx]  in Sheet2.

I hope I have explained that ok

Sub UpdateLog(WS As Worksheet)
Dim MaxRow As Long
Dim oApp As Object
Dim WB As Workbook
Dim WSLog As Worksheet
Dim sFileName As String
Dim cCell As Range

Set oApp = CreateObject("Excel.application")
sFileName = ActiveWorkbook.Path & "\Sign-Off-Log.xlsx"
Set WB = oApp.Workbooks.Open(sFileName)
Set WSLog = WB.ActiveSheet

Set cCell = WSLog.Range("B:B").Find(what:=WS.Range("C5"), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not cCell Is Nothing Then
    WSLog.Range("D" & cCell.Row) = WS.Range("P3")
    WSLog.Range("E" & cCell.Row) = WS.Range("Q3")
End If

Application.DisplayAlerts = False
WB.Close savechanges:=True
Application.DisplayAlerts = True
Set WB = Nothing
Set WSLog = Nothing
oApp.Quit
Set oApp = Nothing

End Sub
0
Comment
Question by:Jagwarman
  • 33
  • 31
64 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40426214
Could you pls post the workbook as they are laid down easier to decrypt !
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40426219
Not really clear as reading it again and again


However, my file has 3 stages so I have put the code in Sheets1, 2 & 3. The code in Sheet 1 is fine and it does exactly what I need. For sheets 2 I need the Macro to open [Sign-Off-Log.xlsx] in Sheet2 and for Sheet3 I need the code to open the file [Sign-Off-Log.xlsx]  in Sheet2.

Not clear at all what you want for Sheet2 and Sheet3 !!
gowflow
0
 

Author Comment

by:Jagwarman
ID: 40426232
I will post files to make it more clear

Thanks
0
 

Author Comment

by:Jagwarman
ID: 40426292
Two files to assist with question.

Test-Input-file V2 has 3 tabs Sheet1, Sheet2 and Sheet3. So this is a 3 part process.

The user should complete in that order but may not.

the cells in C5 and H5 in sheets 2 and 3 are taken from Sheet1 when sheet1is completed  

When the user completes P3 and Q3 in Sheet 1 in [Test-Input-file_V2] it updates the sign-off log. [Log1]

When the user completes P3 and Q3 in Sheet 2 [Test-Input-file_V2] I need it to update the sign-off log in Sheet2 [Log2]

When the user completes P3 and Q3 in Sheet 3 I need it to update the sign-off log in Sheet3 [Log3]

Hope this makes it clearer.
Sign-Off-LogV2.xlsx
Test-Input-file-V2.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40426400
ok fine looking at all this will revert case any question.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40426415
QUESTION

before I go on all this. Are you sure this is the best way you want to do that ? I don't know your process but at this point I may ask you to simply explain in plain English what is the process and what you are trying to achieve and maybe if the way you designed is fine then I will go ahead and if not then I will suggest and will take it from there.

Are you sure you need 3 logs ?? Maybe 1 with the sheet name added will do better ??? Unless you explain the real process I cannot guess.

gowflow
0
 

Author Comment

by:Jagwarman
ID: 40426474
Hmm! that is a very good point, [Are you sure you need 3 logs ?? ]

The process goes across 3 days [although not Consecutive] What we need to do is see that for each item it is signed off on all 3 days.

So

If I understand what you are saying it is "maybe a betterway of doing this would be to have all the sign offs on the one sheet"

And I would be very happy with that, so I guess you mean like this:

Date      Ref      Pay Date      Time      Signed Off D1      Time      Signed Off D2      Time      Signed Off D3
31/10/2014      AA029058      13/11/2014      31/10/2014 10:26      akamer      01/11/2014 10:26      jokelpa      05/11/2014 10:26      jokelpa
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40426560
EXACTLY !


gowflow
0
 

Author Comment

by:Jagwarman
ID: 40426573
ok thanks for thinking of doing it that way as it will be a much better way of doing it

I await the code.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40426574
Again in the same line of thinking do you need 3 sheets ??? if they are similar why 3 ? unless you have different info. Still you did not explain the process. UNLESS it is a total secret .... shuuuut

gowflow
0
 

Author Comment

by:Jagwarman
ID: 40426635
Yes we need 3 sheets because some data can be added, amended and or deleted so Day 2 can be different from day 1 and day 3 can be different from 2 and day 1. it's an Audit requirement to make sure all 3 sheets are visible/available in the future should we be requested to provide the detail.

Regards
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40426850
PLs confirm that the log file will be as follows name and layout.

Further we will update as follows:

1)
Time D1      Signed Off D1: Data that is altered in any Sheet but first occurrence
Time D2      Signed Off D2: Data that is altered in any Sheet but Second occurrence
Time D3      Signed Off D3: Data that is altered in any Sheet but Third occurrence

Is this what you want ?

OR you want as follows:

2)
Time D1      Signed Off D1: Data that is altered in Sheet1
Time D2      Signed Off D2: Data that is altered in Sheet2
Time D3      Signed Off D3: Data that is altered in Sheet3

as your posting of the Log1, Log2, Log3 indicated update this way but your explanation indicated update as per option 1).

Pls advise which one you want.
gowflow
Sign-Off-Log.xlsx
0
 

Author Comment

by:Jagwarman
ID: 40426959
it's definately option 2. Once D1 is complete on Sheet1 that is closed. What happens is Sheet 1 passes the info to sheet 2 so at close of D1 sheets1 and sheet 2 are the same.

on D2 changes can happen on Sheet2 and at close the data is passed to sheet3, so sheet 2 and sheet 3 are the same.

on D3 changes can happen on Sheet3.

So  it's definately option 2.

Thanks and apologies for any confusion and a big thanks for sticking with me on this
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40427083
I am abit confused.

Is it possible that the user will fill Sheet2 first then next day Sheet1 and 3rd day Sheet3 ???
ir
something different like Sheet3 then Sheet2 then Sheet1 ....

??
As you mentioned they may not work in that order.
gowflow
0
 

Author Comment

by:Jagwarman
ID: 40427121
The worksheets are actually labelled PD -2, PD -1 and PD

PD -2 is always completed first. PD -1 is completed next and finally PD.

Regards
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40427734
So can we rename
Sheet1 to PD -2
Sheet2 to PD -1
Sheet3 to PD

Also confusion comes from your previous comment where you mentioned that users may not fill in the same order. Does this means that they may fill PD prior to PD-1 or any other combination ?? It is very important to know.

You need to give me the real situation as if you move the code later it may not work in certain situations.
gowflow
0
 

Author Comment

by:Jagwarman
ID: 40427832
Hi Gowflow, do you not sleep :-)

The sheets can be renamed not a problem.

The procedure is:

On PD-2 the inputter enters details in the cells [as in the file that was attached [Test input file] columns C,F,H and J. other details are input elsewhere on the sheet but are not affected by this Macro. When the details are entered C5 and H5 and J5 will be sent to the 'Sign-off-Log' which is what you did before. All of the details from PD-2 are then copied by the existing Macro in the sheet PD-1.

Someone will check the details on PD-2 and sign it off and thier details from P3 and Q3 are sent to the 'Sign-off-Log' [as before]

At some point in the future the tab PD-1 will be reviewed and either no changes will be made and it will just be signed off. In this case I need P3 and Q3 from the PD-1 tab to go to the sent to the 'Sign-off-Log'

but, and this is where it gets tricky which is why I originall did 3 tabs on the 'Sign-off-Log',

IF changes are made to C5 and/or H5 and/or J5 on the PD-1 tab I need to show the new details on the 'Sign-off-Log' and then when the person signs off PD-1 the details from P3 and Q3 need to reflect against the new record.

The existing Macro will copy all the details from the PD-1 to the PD.

And then at some time in the future the same process as above but obviously there are no further tabs to copy the data from PD to but the  'Sign-off-Log' needs to be updated as before.

I realise this is confusing, it is not to me as I do it all the time. I really appreciate you help.

I don't know if it will help but I have attached a file that shows PD-2, PD-1 and PD where a change has taken place on PD-1 to the rate
Test-Input-file-Gow.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40427850
All is clear and that is what I had forseen and is no problem.

I only have 1 issue that is not clear when you say:


All of the details from PD-2 are then copied by the existing Macro in the sheet PD-1.
....
The existing Macro will copy all the details from the PD-1 to the PD.

What do you mean by 'Macro will COPY' Copy what and what fields ? You have first posted the file Test-Input-File-V2.xlsx that has in Sheet2 and Sheet3 at C5 and H5 a variable pointing to Sheet1 data. Now I see that you can override this do you mean the following:

When PD-2 is done with and just when the log is being updated you want the macro to also:
Put in C5 and H5 of sheet PD-1 the values of PD-2 of these 2 cells.

and

When PD-1 is done with and just when the log is being updated you want the macro to also:
Put in C5 and H5 of sheet PD the values of PD-1 of these 2 cells.

Pls clarify what is meant by 'Macro will Copy'

PS We are on different time zone. I do sleep !!! :)
gowflow
0
 

Author Comment

by:Jagwarman
ID: 40427882
Hi GowFlow,

the original file contains a Macro that gets information from various places and performs a number of calculations. Once it completes it copies the data that is on PD-2 onto PD-1 [and later onto PD] this has been in place for a very long time.

It was decided that the users need to keep a Log and track each of the phases of the file which is where I come in. Originally it looked easy but the more you think through these things the more complicated they become. But maybe the answer is 'keep it simple'.

You said ..............................
When PD-2 is done with and just when the log is being updated you want the macro to also:
Put in C5 and H5 of sheet PD-1 the values of PD-2 of these 2 cells.

and

When PD-1 is done with and just when the log is being updated you want the macro to also:
Put in C5 and H5 of sheet PD the values of PD-1 of these 2 cells.

To clarify:

For PD-2 the original Macro was brilliant and did exactly what I needed.

For PD-1 the details are already in the PD-1 sheet but the user could change them. C5, H5 and J5 If they don't make a change do nothing, if they do make a change I need the to see the new details on the 'Sign-off-Log'  [This is why I originally had 3 Sheets on the 'Sign-off-Log'] and it also need to show who signed it off P3 and Q3 from the PD-1 sheet.

Then same applies to the PD sheet.

I am sorry if I am confusing you even more. I hope I am not
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40427926
ok clear.

I only hope that the macro I will do will not conflict with the macro already there. It is always a challenge we go thru when we have to deal with a 'part of code' as user automatically notice after that they run into problems they don't know why but we had already flagged.

If you can post the Code that you have for this workbook (the entire code) then I will see to make sure that my code will not conflict.

Pls when you post the code press on the Code button in this window it will come out like this (modified to show you)


[code ]
split them to put you code here ...
[/code ]

gowflow
0
 

Author Comment

by:Jagwarman
ID: 40427954
There is stacks of code in the file in 26 modules and I am not allowed to post it.

I hope that will not put you off but if it does I will understand.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40428238
no problem let me work on it.
... 26 modules !!! uff I hope we will not interfere. Question:
Do you have ANY code in the worksheet_Change event of PD-2 PD-1 or PD ?

goto to VBA and doubleclick on each of these sheet and check if there is any code in the Change event and let me know as this is important and could interfere

Also any code in the events of these sheets is important like Activate event Doubleclick Selectionchange Calculate etc...
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40428340
ok here it is.

I am posting both:
1) the log file (re-maked up !!! some formatting to make life easier. If you don't like we can simply revert to default)
2) The new macro with the sheets renamed as per your comments.

Please check it and make your tests and let me know.
gowflow
Test-Input-file-V02.xlsm
Sign-Off-Log.xlsx
0
 

Author Comment

by:Jagwarman
ID: 40428526
it's looking good but I will need to test it over the next couple of days.

Thank you [again] gowflow for the time and effort you have put into this for me very much appreciated.

Have a great weekend
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40428537
ok fine hv a good weekend too.
gowlfow
0
 

Author Comment

by:Jagwarman
ID: 40432426
Hi Gowflow,

I have now had a chance to test this and you have done a really great job, unfortunately though one bit does not seem to be correct.

In our conversation above it said:

At some point in the future the tab PD-1 will be reviewed and either no changes will be made and it will just be signed off. In this case I need P3 and Q3 from the PD-1 tab to go to the sent to the 'Sign-off-Log'

but, and this is where it gets tricky which is why I originall did 3 tabs on the 'Sign-off-Log',

IF changes are made to C5 and/or H5 and/or J5 on the PD-1 tab I need to show the new details on the 'Sign-off-Log' and then when the person signs off PD-1 the details from P3 and Q3 need to reflect against the new record.

>>>>>>>>>>>>>>>>>>>>>>>>>>

In my testing the results are :

                                                                                     PD-2            PD-1            PD      
Date      Ref      Pay Date      Time      Signed Off      Time      Signed Off      Time      Signed Off
Nov-10, 2014      AD295856      Nov-16, 2014      Nov-10, 2014 10:25 AM      JK      Nov-08, 2014 12:00 AM      John            
Nov-10, 2014      AD295856      Nov-17, 2014                                    
                              
So, on PD-2 the record was
Nov-10, 2014      AD295856      Nov-16, 2014

and on PD-1 the record changed to

Nov-10, 2014      AD295856      Nov-17, 2014 [The date changed]

so the sign off for John on PD-1 should have been appended to the line with the date AD295856      Nov-17, 2014

Am I doing something wrong.

I feel real bad because you have already put so much into this.

Kind Regards
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40432602
Well first of all I wish to clarify something:

It is our job to get the solution 'correct' to the asker and it is the asker's job if I can say or interest to do as much as testing as possible to make sure that the proposed solution is correct and accurate. In case the solution proposed lacks preciseness and is not correct then it is the 'duty' of the Expert to make sure to deliver a 'correct' solution or else he will bear the consequences of not doing so among which having the question deleted and also contributing to having the asker leave the website as he/she did not receive full satisfaction.
 
All this being said is to first of all confirm my regards to your testing and also advise that it is entirely my oversight and mistake and you should not worry about time spent at all as our satisfaction is entirely derived from providing a complete working solution.

Now lets get to specifics.

All along I was under the impression that the key was the ISIN number reason why you got this behavior as the date was changed and the ISIN was not changed so it updated the first record that it found which was the Nov-16, 2014 and not the Nov-17, 2014 as it came afterward.

Also looking very carefully I noticed a flaw in the code which would be would also give you the previous result but in a different manner as follows:

In a remote case where your user signs off the sheet not changing C5 and H5 (it would update the existing record ie Nov-16, 2014 with the sign off P3 and Q3 to just after the user notices that he should have changed C5 or H5 and proceed to do same and then the record would create a new instance with Nov-17, 2014 with no sign off and with the old sign off in Nov-16, 2014.

All this now leading to multiple issues and total different behavior of these macro as they open the door for wrong data to be possibly updated due to normal behavior of users that may be unpredictable.

This being said I will wait for you to read and advise your comments till here and then based on your reply I may have suggestions but surely code need to be modified accordingly and totally.

gowflow
0
 

Author Comment

by:Jagwarman
ID: 40432788
Hi Gowflow,

Appreciate your quick response

I have had a long discussion with the users to tie this thing down once and for all. They have confirmed that C5 would never change on PD-1 or PD so this is set in stone.

C5 will carry through from PD-2 to PD-1 and PD.

So my original statement would now read:

At some point in the future the tab PD-1 will be reviewed and either no changes will be made at all and it will just be signed off. In this case I need P3 and Q3 from the PD-1 tab to go to the 'Sign-off-Log' and be reflected in the same row as the original item under the heading PD-1

but,

IF changes are made to H5 and/or J5 on the PD-1 tab I need to show the new details on the 'Sign-off-Log' [so now there will be 2 records with the same ISIN] and then when the person signs off PD-1 the details from P3 and Q3 need to reflect against the new record.

The same rule applies to the PD sheet

many thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40433158
ok as to my knowledge there was never question of J5 which is very new to me and was not taken into consideration so far.

So shall we base now on C5 H5 J5 being modified and once all are not blank then to check log file to either create new if not existing or update if already there with the P3 Q3 data ? I would still say C5 as regardless if your user said and confirmed it will not change then by the mere fact that we use C5 as part of the Key then we need to check its status.

Furthermore we need to update the logfile to add 1 column which will be J5 value in Col D ??? like:
Col A       B      C               D
Date      Ref      Pay Date  Rate

and will need to check if the TRIO Ref + Pay Date + Rate already exist.. then update P3 and Q3 and if any of these 3 items have changed then Create new and update P3 and Q3 on new ?? is this correct ?

1 more issue you did not comment on the fact that users may signoff 'first' then make some changes ... you agree that this may happen and we need to deal with this (and this again regardless whether your users will say: NO SIR we will never do this !!! the system allow users to go either way and reason for this will need to deal with this situation.)

Pls advise
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40433617
Well having said above and looking at the whole issue the situation is well more complex that you even yourself though it would be.

Anyhow I will not start dissecting all possible situations (as I already did myself and did comeup with the solutions) instead I will kindly ask you to checkout this new version as well as the attached Logfile and please perform all the tests that you can do to see if all possible situations have been covered.

Please let me know your comments.
Now if I did not understand your request and am off-course then please clarify what should be and what should not be.

Regards
gowflow
Test-Input-file-V03.xlsm
Sign-Off-Log.xlsx
0
 

Author Comment

by:Jagwarman
ID: 40434535
Hi Gowflow,

I have done some testing and it looks very good so far. I have a colleague testing now so will be able to confirm later this morning but if they get the same results I did it  [as the saying goes here] does exactly what it says on the tin
0
 

Author Comment

by:Jagwarman
ID: 40434715
Hi Gowflow one thing we are finding is that the file sign off log is not closing out and when we look in Task Manager it is still there. Any ideas?

Thanks
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Jagwarman
ID: 40434728
and Just spotted something else. I have some code in my 'actual' file [at this point I am in PD-2]

Range("J6").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

at this point the Macro is going to

Private Sub Worksheet_Change(ByVal Target As Range)

Would you know why this is?

Thanks
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40434732
Your are correct !!! on the Tin !!

here is the fix. Pls test it as depending on Excel version we have different behaviors.
gowflow
Test-Input-file-V04.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40434736
well for your last comment

I told you I am not responsible for the code you already have sorry I cannot comment on part of code this is totally unprofessional.

I asked to get the code that was affecting the sheets but you did not provide same so sorry I am not a fortune teller.
gowflow
0
 

Author Comment

by:Jagwarman
ID: 40434870
Thank you Gowflow I will test that and get beck. Apologies for my last Question
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40434873
If you have any trouble with code getting mixed with other code then the normal and usual path is not to get issue confused into 1 question but to deal with it in a new question by posting all relevant code so Experts can look at it.
gowflow
0
 

Author Comment

by:Jagwarman
ID: 40434920
ok thank you for the advice
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40434929
No offence for sure !
gowflow
0
 

Author Comment

by:Jagwarman
ID: 40435026
likewise :-)
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40435042
I knew this would happen especially when you said 26 modules !!! no matter how good one can be in programing you can never tell how other may code.

Anyway the sample of 2 lines you posted clearly shows it is AMATURE coding that is very poor and usually lead to such behaviors.

Usually when coding is done as implicit coding
Range("A1") assumes that it is cell A1 of the active sheet. But active sheet in coding can change could now be Sheet1 and later in code be Sheet25 and if code hits now Range("A1") it refers to cell A1 in sheet1 and if it reaches it later then it is A1 of sheet25 which leads to total unpredictable behavior.

Reason why you see in my code is it all Explicit with variable declaration and which cut the way short to any mis-behaviour.

gowflow
0
 

Author Comment

by:Jagwarman
ID: 40435102
thanks Gowflow you have taught me a great deal over the last week. I hope to complete the testing soon and sign off. I have now been able to resolve the issue relating to my 'last' question as a result of your comments.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40435282
Good but if you should be careful as to working with code unless your a pro as sometimes you may seem to fix things apparently but in the background you may be messing up other things.

Good luck anyway.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40437429
Any news on your endeavors !!! ?
gowflow
0
 

Author Comment

by:Jagwarman
ID: 40437730
Got pushed to sort something else out today. will be back on it tomorrow.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40437988
ok fine no sweat.
gowflow
0
 

Author Comment

by:Jagwarman
ID: 40440150
Hi gowflow.

We might have to give up on this. :-(

when I use the file Test-Input-file-V04.xlsm it works fine. When I put the code into my file the first part works fine, no problems what so ever. It records the item[s] perfectly

Date      Ref      Pay Date      Rate      Time      Signed Off      Time      Signed Off      Time      Signed Off
Nov-13, 2014      CH0247163726      Sep-30, 2014      0.1254                                    

When I do the Authorise, the file Test-Input-file-V04.xlsm works perfectly. when I put the code into my file I get an error on this line of code

WSLog.Cells(cCell.Row, lCol) = WS.Range("P3")

in this section>>>>>>>>>>>>>>>>>>>>>>>

'---> New Way for Updating and Deleting Signoff
With WSLog.Range("B:B")
    Set cCell = .Find(what:=WS.Range("C5"), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If Not cCell Is Nothing Then
        sFirstAddress = cCell.Address
        Do
            If cCell.Offset(, 1) = WS.Range("H5") And cCell.Offset(, 2) = WS.Range("J5") Then
                If bRevertSignOff Then
                    WSLog.Cells(cCell.Row, lCol) = ""
                    WSLog.Cells(cCell.Row, lCol + 1) = ""
                Else
                    WSLog.Cells(cCell.Row, lCol) = WS.Range("P3")
                    WSLog.Cells(cCell.Row, lCol + 1) = WS.Range("Q3")
                End If
                UpdateLog = True
                Exit Do
            Else
                Set cCell = .FindNext(cCell)
            End If
         Loop While Not cCell Is Nothing And cCell.Address <> sFirstAddress
    End If
 End With

>>>>>>>>>>>>

The error is Run time error 1004
Application defined or object defined error

Sorry
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40440497
There is no Sorry !

Post the Code that you have in this worksheet by going to VBA and doubleclick on this sheet and then copy all the code that is there and paste it here.

Note press first on the button Code in this window it will give you 2 labels surrounded by brackets like below.
[code ] [/code ]

Separate them and include your entire code in between so it looks like this:
[code ]
.. put your code here
 [/code ]

Never ABANDON !!! Shame on you !
gowflow
0
 

Author Comment

by:Jagwarman
ID: 40441994
gowlow you said "Post the Code that you have in this worksheet " Not exactly sure what you mean by "this worksheet"
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40442071
If you are afraid of confidential data and have no problem with the code that is in the workbook do this:

1) Make a copy of the workbook the entire production one that you have that also have my code
2) From the worksheets delete all the data but keep the worksheets (like click on each worksheet and delete all the data) keep buttons or dorpdowns if any
3) Once done you can now save this workbook and attach it here it would have basically all the code but no information !

NOTE
If the solution I propsed is fine and working in the context that I delivered it under then I suggest the following (as this maybe a very long process)

Close this question
and then open a new one and label it continuation of this one and explain in few lines the problem and attach this new workbook and will take it from there.

Maybe we open then a total complete revamp of your code to be a bit more to standard. Excuse me if I am insinuating that what you have is not standard but fact that you are having issues maybe a result of poor programing.

gowflow
0
 

Author Comment

by:Jagwarman
ID: 40442343
Hi gowflow,

it works, it was entirely my fault, in the heet names instead of PD-2 I had a space PD -2 and could not see for looking.

So it works fine for me but for a colleage who is overseas using Citrix to access the files it does not even find the Macro. I input a couple of  MsgBox to see if they could see them but No they could not. So I guess we have an internal issue I now need to sort.

Thanks for all of your hard work, your commitment and your patience with me.

Have a great weekend.
0
 

Author Comment

by:Jagwarman
ID: 40442345
I cannot accept the solution on my work PC I will do this later today from home.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40442380
ok glad this was sorted out. Meantime I can also help with your abroad issue as the macro need to be set differently. Anyhow pls post a link in here for any issue you may need help with.

Best Regards
and have a good weekend

gowflow
0
 

Author Comment

by:Jagwarman
ID: 40442388
just the abraod issue everything else is working
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40442401
no problem once your set and clear this one pls put the specifics in a new question with a sample or how you guys work and then will deal with it.
gowflow
0
 

Author Comment

by:Jagwarman
ID: 40446651
gowflow

trust you had a good weekend.

I am more than happy to post a new post but When you say " pls put the specifics in a new question with a sample or how you guys work" I am not sure what you mean.they enter the details in the cell and it does not kick off the macro.

Are you saying I should post the file Test Input File V04 ?

Thanks
Regards
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40447055
Well this is news to me !! as you never told me what was the problem

The only thing I understood is that it was working correctly in the file I posted and when you moved it to your production file it was not or things were messed up with no specifics.

So now you say it is not kicking the macro for this here are 2 questions:

1) In your production file the cells that kicks the macro should be exactly the same cell (in reference) as the ones in the file which are: C5, H5, J5, P3, Q3
So are they the same in production file ?

2) The macro is in the worksheet Change event of each of PD-2, PD-1 and PD is it in the production file ?

3) the rest of the macro which are the Sub CreateLog and UpdateLog are in a Module. Are they in the Production file ?

Answer these first then will try to solve it here.
gowflow
0
 

Author Comment

by:Jagwarman
ID: 40447148
Hi gowflow,

I feel real bad. last week my colleagues said it was not not working for them using Citrix. Today we have just finished testing and it is working fine so I can only assume it was an interanal system problem.

What you did was brilliant, you have been totally committed to completing this and have been extremely patient with me, my requirements and my issues.

A big thank you.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40447172
Well you only make me happy and satisfied as this is what we aim for. Deliver a complete and working solution.
Let me know when you need more help by posting a link in here.
gowflow
0
 

Author Comment

by:Jagwarman
ID: 40447176
:-)
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40447550
Just for me to understand shall I consider the matter okay and no more attention needed ?
gowflow
0
 

Author Comment

by:Jagwarman
ID: 40447596
yes I can't sign off from this PC [not sure why] will sign off later tonight from my home PC

thanks again
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40447648
No problem, I surely know why, your restricted for certain sites from your company !!!
Cheers

gowflow
0
 

Author Closing Comment

by:Jagwarman
ID: 40451863
Excellent Expert prepared to persevere and very patient.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now