How to set currency with different starting values

[paulked]

Sorry, forgot to change the csv location back!

[paulked]

    Workbooks.Open "C:\temp\prcbook.csv" '("C:\Users\Administrator\Desktop\W\Reports\PRCBOOK.CSV")

to

    Workbooks.Open ("C:\Users\Administrator\Desktop\W\Reports\PRCBOOK.CSV")

[paulked]

With button:

[GillsITWorld]

The problem with a button is that this is an automated process on Sunday early morning. No humans are available.

[paulked]

Did the DoEvents do it? If not, try uncommenting the line in the Workbook_Open code and change the time in that to 00:00:10, if there is no one about then an extra 10 seconds shouldn't matter

[GillsITWorld]

Ok, just ran the DoEvents and it worked fine while observed. The real test will be this Sunday during normal operation.

So assuming that this current code is good I also have 2 other PriceBooks I'm trying to work with.

So the next question would be how to add an additional 15% onto the currency field, before or after it's been converted.

[paulked]

                ar(i, 7) = (ar(i, 7) / 1000) * 1.15
            Else
                ar(i, 7) = (ar(i, 7) / 100) * 1.15

[GillsITWorld]

Beautiful! Absolutely Beautiful!

As of now, all tests have worked flawlessly.
I have created different macro files for the different price books and this Sunday it will run per my auto scheduler and email out.

Just in case I do start having issues where do I put the

If not, try uncommenting the line in the Workbook_Open code and change the time in that to 00:00:10

Thank you very much for the assistance

[paulked]

In the ThisWorkbook code module:

67262.jpg

Private Sub Workbook_Open()
    Application.Wait (Now + TimeValue("00:00:10"))
    PRCBOOK_Open
End Sub

[paulked]

Happy to help, hope it all goes okay for you

[GillsITWorld]

So far absolutely fantastic.

Just about done, working on the last part of the pricebook now.

How would I have it open another workbook called "BlankQuote" and copy sheet1 into the first workbook as the first sheet and then close the second workbook?

[paulked]

Sub CopySheet()
    Application.ScreenUpdating = False
    Workbooks.Open "C:\temp\blankquote.xlsx"
    Sheets("Sheet1").Copy Before:=ThisWorkbook.Sheets("Sheet1")
    Sheets("Sheet1 (2)").Name = "New Sheet"
    Workbooks("blankquote.xlsx").Close 0
End Sub

[GillsITWorld]

Ok, here is everything.

What seems to be happening is that it's taking the .xlsm as the active and pasting stuff to it?

QuoteSheet.xlsm
BlankQuote.xlsx
PRCBOOK.csv

[paulked]

I took it you wanted to put the blank quote sheet into the macro workbook. Do you want it to go into the csv? If so, it will have to be saved as a workbook first and then the sheet added (only one 'sheet' in a csv!)

Is that what is supposed to happen?

[GillsITWorld]

My apologies, yes, basically I'm attempting to output this as single document so that I can email it to Sales people to use daily.

Then we can move the save line to after the formatting then add the copy and paste. (Tried it, but still didn't work)

[paulked]

1 minute!

[GillsITWorld]

Please take your time! You have become my new best friend!

[paulked]

Try this.

I noticed the 15% wasn't in, just in case you've missed it out

[GillsITWorld]

So far perfect! This one doesn't use the 15% that was for the 2nd workbook. But great looking out!
I'm going to run this and double check all sheets tomorrow but the preliminaries look good!

[paulked]

Excellent! Let me know how it goes, stay safe!