VBA - Paste / Select Column / Currency / format

Hi

I have got following issues with the code attached:

I am trying to paste selected formula cells till the end of the adjacent left column. In "Selection.Autofill..." line I am getting a type mismatch error.

Input grand total for only "Total USD" and "Total GBP" columns.

Once these cells are popullated I have to select alternative columns and apply USD and GBP currency to it and finally format these selected columns same as the grand total column next to it.

The reason I have used offset method is that my actual cell at the start changes everytime my pivot table gets refreshed. Hope this explains my query.

Any help is greatly apriciated.

Please Login or Register  to view this content.

Regards,
Rik

Excel file Attached. Please ignore image.

The autofill line shouldn't have = ActiveCell.Value at the end after it and the destination range shouldn't have .Value

Try this.

Please Login or Register  to view this content.

If that doesn't work you can also try this.

I think it works but can't check the formulas.

Please Login or Register  to view this content.

This will still not work.

Please Login or Register  to view this content.

This works seamlessly. Can we add other attributes to this as well - selecting alternate columns and apply currency format/apply format of grand total.

Please Login or Register  to view this content.

[/QUOTE]

Thanks a lot for your help.

Yes you could do all that.

Norie,

How can we select alternative columns in order to format them for currency?

Regards,
Rik

Rik

Which columns have to be formatted and with which currency?

PS Does my code go one row to far down? When I tried it in your worksheet it seemed to.

Noorie,

Below columns should be formated in the currency mentioned:

"USD Daily Rate"
"GBP Daily Rate"
"USD Hourly Rate"
"GBP Hourly Rate"
"Total USD"
"Total GBP"

Duhh...never noticed it but yes your code goes one row too far down.

Also, will require to paste format of "grand total" column to all these above mentioned columns. Remember the position of "grand total" will change everytime spreadsheet is updated.

Cheers,
Rik

Rik

What do you mean 'paste format of "grand total"' column?

The grand total column is formatted as General, I thought you wanted the columns with the formulas formatted as currency?

Anyway, if you do wany currency formatting try this.

You better check the formatting - there's something up with mine and it just shows £ whatever symbol you specify.

Please Login or Register  to view this content.

PS The position of 'grand total' shouldn't matter, the code doesn't have hard-coded range references.

The VLOOKUP formula might need adjusted though because that does.

Noorie,

You are right, it shows £ sign for all the values rather than for alternate column.

What I mean about "Grand Total" format is that I need the format of that column applied to all these additional columns as well. But we will need to apply this format before we apply currency format otherwise currency format will be lost.

Vlookup should be fine as the range in this formula will not change even after spreadsheet gets refreshed.

Rik

Rik

The format of the Grand Total column is General, if you apply that format to the other columns it will be lost when the currency format's are applied.

Try this for the currency formatting, it worked for me.

Please Login or Register  to view this content.

Noorie

That works fine for currency and you are right if we apply "grand total" format now then it will lose currency format.

But that is the reason we need to apply "grand total" format before the coding of currency format. I will need borders and text alignment format from "grand total" column.

You never mentioned that sort of formatting.

Please Login or Register  to view this content.

Apologies for that Noorie

In your current code it does not format the headings "USD Rate",etc same as "Grand Total" format.

I've got to go, why not try that one yourself.

Tried few things but except that line everything else changed

Anyways, thanks for all your help really appreciate it.

Cheers,
Rik