Need a hand to clean up working code (External Data fetch foreign exchange rates)

I have created some code to fetch the USD exchange rate from a bank website. When the workbook is opened, it runs in the background to fetch the data from the website and then displays the Buy/Sell rate and the date & time that these rates were current at.

The code works fine however it was originally created using Macro Recorder. I have tidied it up from the original recording however it needs improving. What I am looking for is a condensing (is that a word?) of the 'data cleaning' section (lines 370-540) and remove the need for having "temp" sheets (lines 60-130 and line 540).

This should be very easy for any intermediate or advanced VBA user. Any one like to try?

Please Login or Register  to view this content.

A few things, not much:

Please Login or Register  to view this content.

Thanks for the assistance JBeaucaire. However it doesn't remove the need for the 'temp1' & 'temp2' sheets.

I use junk sheets all the time to massage data into submission then copy it to my main sheets. A fine method. I see no reason to rewrite a working macro simply because it does this.

Can you post what your data looks like after downloading from the website and what you want it to look like afterwards? From what I see you only need data from two cells? (Range("C2:D2") from temp2 sheet at the end.)

Originally Posted by RHCPgergo

From what I see you only need data from two cells? (Range("C2:D2") from temp2 sheet at the end.)

Hello RHCPgergo,

Thank you for your assistance.

I don't actually require "temp2". A new sheet would do just as well. (I would like to do away with the two temp sheets and replace with an "add new sheet")

All I want/require in the output is the following data:

1. USD buy rate
2. USD sell rate
3. Currency (i.e. "USD")(This is a visual check to ensure the macro is picking up the correct currency).
4. The date & time the rates were current at (This is included in the data downloaded from the website)

The cell positions in the output are unimportant (they don't have to be C2:D2 etc). The only condition on this is that I need the buy & sell rate in two adjacent cells so I can easily copy/paste into another excel book.

As you can see from the above - it is very flexible!

Originally Posted by RHCPgergo

I wonder why the webquery only works with "Public Sub". It refuses to work with a simple "Sub" procedure.

This is very close to what I was looking for! Thank you.

Re: Public Sub: Hmmm. I hadn't noticed that it didn't work without it. I like to declare all functions & subs as Public if they are not private (Yes I know they are Public by default but I like to show it. I also insist on Option Explicit on all my modules! )

The code from RHCPgergo works for me so I will mark this thread as solved.