Excluding VAT for selected item
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. |
Excluding VAT for selected item
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. |
Excluding VAT for selected item
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. |
Excluding VAT for selected item
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 __________________________ |
Excluding VAT for selected item
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. |
All times are GMT +1. The time now is 07:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com