So I'm trying to copy the contents of one sheet onto another sheet then change the sheet name reference in the formulas in the copied sheet. The problems is when I change TESTER to EP+CB1, the reference wont work correctly.
Please Login or Register to view this content.
Last edited by Lotrking1010; 12-31-2012 at 08:15 AM.
Hello Lotrking1010,
Change this loop and see if this helps the problem.
Please Login or Register to view this content.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
no it doesn't seem to make a difference. I do know before it auto changed EP+CB1 to EP+'CB1', but no I don't know because i cant get the code to stop without closing excel. Is there a way to auto cancel pop up boxes? Because now when the codes runs i get a pop up box for me to select the destination of the bad reference.
Hello Lotrking1010,
If you can you post a copy of your workbook. There seems to a lot going and without the benefit of seeing all the code, it could take a very long to solve this. If your workbook has any proprietary information, please redact it before you post.
Ok I've attached a sample workbook that should have all the required information. the real issue seems to be that when i use a variable to as replacement for part of the formula it makes the formula not work correctly. specifically when I have the formula:
=IF(TESTER!B6="16",IF( TESTER!C6="BLU",TESTER!K6,""),"")
I have the code find and replace every occurrence of the word TESTER with the name of the starting sheet, in this case EP+CB1. the problem my be that when i use the variable strOS as the replacement text it is coming out as EP+'CB1' instead of EP+CB1 and I;'m not sure why or how to fix it.
Hello Lotrking1010,
I am not following what you want to do with these formulas on the new sheet. They link back to the "WC Formula" sheet cells that in turn link back to the "Tester" worksheet cells. What is the intended formula result?
The formulas on the new sheet are supposed to be changed by the find and replace function that is not working properly. also the point of this macro is to start from the sheet with the data on it, in this case EP+CB1.
the whole idea is you start with some sheet of data (see EP+CB1). Then from there you run the macro and it creates the new sheet called wire count ###. then the formulas are copy from the WC Formulas sheet and placed in this wire count sheet. then the marco should run a find and replace on the wire count sheet to change the sheet reference from the TESTER sheet to the desired sheet (in this case EP+CB1). Does that make sense?
what i want is for the formulas on this new sheet to be exactly like those on the WC Formulas sheet except the ones in WC Formulas sheet look at the TESTER sheet and the new new formulas should look at the sheet you had active when you hit run, in the case EP+CB1.
Hello Lotrking1010,
This version of the macro works on my end. Give it a try and let me know the results.
Please Login or Register to view this content.
It works like a charm! Thank you very much.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks