How to set currency with different starting values

[GillsITWorld]

How would I hide sheet "prcbook"?

[paulked]

    Worksheets("prcbook").Visible = False

[GillsITWorld]

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

[paulked]

time for sleep now, it's 4:30am here

[GillsITWorld]

No worries, completely understand! I'll talk with you tomorrow!

[GillsITWorld]

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?

[GillsITWorld]

Also Updated the BlankQuote it was pulling from.

BlankQuote.xlsx

[GillsITWorld]

I guess what Im looking for is

How to Copy Exact VLookup Formula in VBA without changing the cell reference?

[GillsITWorld]

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.

[paulked]

Hmmm... Where is the data held for the vlookup?

[GillsITWorld]

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)

[paulked]

Rushed a bit, but try this

[GillsITWorld]

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.

[paulked]

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.

[GillsITWorld]

This one with formulas won't go out this Sunday, just the other 2 PriceBooks we worked on.


Below are the attachments

BlankQuote.xlsx
PRCBOOK.csv
QuoteSheet.xlsm

Again, thank you for ALL the support you have given me!

[paulked]

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!

[GillsITWorld]

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!

[paulked]

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
BlankQuote.xlsx
QuoteSheet.xlsm

[GillsITWorld]

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.

[paulked]

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.