I am using a spreadsheet which uses a macro to copy and pasted products onto
a selected area on the same worksheet. One of the items requires 0 VAT. Is
there anyway this can be achieved when the item could appear in one of 46
rows, it is always the last item to be shown on the pasted list, although it
is dependant on what is pasted above it as to which row it will be pasted.

What's the VBA code you're using for the macro? Essentially you would need
to modify it to tell Excel to set the VAT for the last row of the relevant
range to 0.

If you don't know how to view the code behind a macro, right-click the tab
in which the macro operates and select View Code. Then copy all the code
into this post so that someone can take a look at it.

Dave
--
Brevity is the soul of wit.


"triffidbook" wrote:

I am using a spreadsheet which uses a macro to copy and pasted products onto
a selected area on the same worksheet. One of the items requires 0 VAT. Is
there anyway this can be achieved when the item could appear in one of 46
rows, it is always the last item to be shown on the pasted list, although it
is dependant on what is pasted above it as to which row it will be pasted.

Calculate the last row like so

LastRow = Cells(Rows.Count,"A").End(xlUp).Row

change the "A" to the actual column.

Then you can add vat to that item.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"triffidbook" wrote in message
...
I am using a spreadsheet which uses a macro to copy and pasted products
onto
a selected area on the same worksheet. One of the items requires 0 VAT.
Is
there anyway this can be achieved when the item could appear in one of 46
rows, it is always the last item to be shown on the pasted list, although
it
is dependant on what is pasted above it as to which row it will be pasted.

One other approach, assuming your data caters for it, is to hold a
code for the level of VAT against each product.

e.g. Product A (standard rate) code TS
Product B (zero rated) code T0
product C (Exempt VAT) TE
Product D (Reduced Rate) TR

Then when your macro copies the product, if it also copied the VAT
code, your VAT calculation formula could be made to be automatic by
using a lookup of the VAT code in a simple VAT rate table.

If you don't want to carry an extra field for VAT, you might like to
consider building the VAT code in with the Product name or code.

e.g. Product A (TS)

then use some string slicing in the VAT formula to identify the two
character VAT code.

HTH


On Wed, 6 Sep 2006 07:09:03 -0700, triffidbook
wrote:

I am using a spreadsheet which uses a macro to copy and pasted products onto
a selected area on the same worksheet. One of the items requires 0 VAT. Is
there anyway this can be achieved when the item could appear in one of 46
rows, it is always the last item to be shown on the pasted list, although it
is dependant on what is pasted above it as to which row it will be pasted.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

The VBA code is:

Sub PARTS()
'
' PARTS Macro
' Macro recorded 05/09/2006
'
' Keyboard Shortcut: Ctrl+Shift+X
'
Range("A5").Select
Range("A4:D1571").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _
"A1:A2"), CopyToRange:=Range("F4:I28"), Unique:=False
End Sub

"Dave F" wrote:

What's the VBA code you're using for the macro? Essentially you would need
to modify it to tell Excel to set the VAT for the last row of the relevant
range to 0.

If you don't know how to view the code behind a macro, right-click the tab
in which the macro operates and select View Code. Then copy all the code
into this post so that someone can take a look at it.

Dave
--
Brevity is the soul of wit.


"triffidbook" wrote:

I am using a spreadsheet which uses a macro to copy and pasted products onto
a selected area on the same worksheet. One of the items requires 0 VAT. Is
there anyway this can be achieved when the item could appear in one of 46
rows, it is always the last item to be shown on the pasted list, although it
is dependant on what is pasted above it as to which row it will be pasted.