How would I hide sheet "prcbook"?
Printable View
How would I hide sheet "prcbook"?
Code:Worksheets("prcbook").Visible = False
haha, I had it close, I just spelled "Visible" incorrectly!
Something I just noticed is that somehow the formula on the completed sheet has added info to the formula in reference to the sheet location but that's not in the original formula
time for sleep now, it's 4:30am here:thumb
No worries, completely understand! I'll talk with you tomorrow!
Ok, so working on the last WorkBook the BlankQuote, when it copies the page from the workbook "BlankQuote" it's coping over a Vlookupformula and when it copies and pastes the formula it changes the reference from Sheet2 to #REF in the actual formula.
Would the easier fix be instead of coping the Vlookup code into pricebook, coping the Prcbook into Sheet2 of the BankQuote Form and then have it resave that output?
Also Updated the BlankQuote it was pulling from.
Attachment 26531
I guess what Im looking for is
How to Copy Exact VLookup Formula in VBA without changing the cell reference?
I believe this is all happening because of the Vlookup on Sheet1 referencing Sheet 2, when trying to move it between workbooks it wants to update the links and it's causing a problem.
Hmmm... Where is the data held for the vlookup?
Thats coming from the prcbook, it should all be on sheet2 be the end of it
Sheet1 should be the QuoteForm (Labeled Sheet1)
Sheet2 should be the PrcBook (Labeled Sheet2)
Rushed a bit, but try this
Ok, so now it seems to be having a problem with there being 2 sheets named "Sheet2"
Run-Time Error '1004':
That name is already taken. Try a different one.
Get rid of your sheet2 in the blankquote. I haven't seen that sheet but I guess then you will get your ref! back! I'd run with giving them a blank quote sheet (with no formula) for this Sunday, let's take a look next week. If you send me a working copy of the blankquote (ie with two sheets and vlookup working) I can take a look over the weekend.
This one with formulas won't go out this Sunday, just the other 2 PriceBooks we worked on.
Below are the attachments
Attachment 26535
Attachment 26536
Attachment 26537
Again, thank you for ALL the support you have given me!
No worries! Have a good, and safe, weekend. We are about to have a 'Zoom' drinks party with a load of friends... if you can't join them, beat them!
Awesome! You as well, be safe in this crazy world and have a great weekend!
I'm also available all weekend if you have any other questions!
Ok, I think I've sorted it.
Operation:
Schedular opens QuoteSheet.xlsm which then
Opens PrcBook.csv
Re-calculates prices, removes unwanted columns and formats text (do you need to format the text?)
Opens BlankQuote.xlsx
Copies the modified PrcBook to Sheet2 in BlankQuote
Saves BlankQuote with data and time stamp
Closes BlankQuote
Closes PrcBook without saving changes (keeps it intact for any other process)
Quits Excel
I took the liberty of making the following changes to the BlankQuote, please feel free to change to whatever you really want!
Changed the formula so that it is a blank form unless an item is entered or there is an error
Put the average value of the percentages at the bottom of the column
Locked all cells apart from the Item # and Competitors columns and the Contact info. (added a line "Additional Info:" which is also unlocked)
All seems to be fine and dandy, but let me know if you need any further assistance.
Cheers and stay safe :thumb
Attachment 26547
Attachment 26548
Ok, so Im not sure if this is copying the actual prcbook to page 2, any time you try and put an item number in it comes up item not found.
Then when it saves Blankquote with date and time stamp its saving only the prcbook to this workbook.
Then Blankquote just looks like it has the quote form without the prcbook.
I just tried it again and no problems, see attached.
What's on sheet2? It should be the modified csv file like in the attached.
Did you run an earlier version by mistake?
Have you put the new BlankQuote in the directory C:\Users\Administrator\Desktop\W\Reports\?
Are you opening the correct BlankQuote? It will have the timestamp on it similar to my attachment above.
Ok it was me, I had the BlankQuote.xlsx in the incorrect location. I fixed that.
Then I had to not delete row1 so that item # 1221 would work.
Then I re added the line to hide sheet 2.
I'll have the team go over and make sure it works for them as intended.
This is great! You have been a substantial help!
Other than adding rep to you how else can I repay the time and effort?!
No need for repayment, I'm a VBA intern and learning is payment enough! It is nice to help people, especially in these unprecidented times.
Take care and stay safe :thumb
PS Thanks for the Rep!
BTW, while I was testing I used a count-down routine that enables you to interupt the macro within 10 secs of opening the file. If you don't press a button within the 10 secs the macro runs as normal. You may find it handy for testing yourself, so I've attached it.
Just wanted to give an update! Everything is working beautifully, all 3 sheets have been successfully implemented into routine!
Again thank you very much, I am now excited to start looking for other VBA projects to optimize time!
Excellent! Stay safe :thumb
Hello again!
So far everyone is very happy and all of the Pricebooks/Quote Forms are working beautifully!
So again, thank you!
So I have a new VBA Project, should I continue here, or make a new thread?
Hi. Glad it's all okay :thumb
You would have to start a new thread, Stay Safe!
And mark this one [SOLVED] using Thread Tools above your first post