macro for invoice totals

I have this macro that inserts lines and copies a invoice header.

Now I want to total the invoice.

In the first row after a value in B in E the word totals must be inserted, 
H, I and K must be summed the amount of lines differ on each invoice but 
there is a heading from where it must be summed. CTNS(H), QTY(I), Total(K) 

Thanks a lot. 



Public Sub Deilv2()
Dim LastRow As Long
Dim row_index As Long
Dim rng As Range
Set rng = Range("B2:K25")
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row
For row_index = LastRow - 1 To 26 Step -1
If Cells(row_index, "B").Value <> _
Cells(row_index + 1, "B").Value Then
Cells(row_index + 1, "B").Resize(26).EntireRow. _
insert Shift:=xlDown
rng.Copy Destination:=Cells(row_index + 1, "B").Offset(2)
End If
Next
Application.ScreenUpdating = True
End Sub

0
Esrei1 (23)
8/11/2005 1:29:01 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
497 Views

Similar Articles

[PageSpeed] 59

Reply:

Similar Artilces:

Macro for removing columns/rows, freezing panes etc?
Hi all, I have only been using Excel for about 4 months and in the process of learning new things. I want to make use of macros to automate the repetitive tasks I do every day, but just when I think I've mastered each one, I get error messages. To make myself clearer: I work in a call center, and every day I have to sift through about 8 *.xls files containing raw data to gain figures about call volumes. I then copy and paste the relevant figures into a properly formatted Master worksheet with formulas etc. The tasks I do every day are repetitive, but I don't know whether these type...

Macro for moving sheets
Hi I have tons of data files in ASCII format. I want to move all individual files as sheets in one excel file. Is there anyway to program a macro to do this? It can be done, and the best way to get started is to record the macro to perform the operation once, then alter the macro to accommodate the rest of the files. Additionally, you may need to have 2 files open at once: the "master" file that holds the imported text files, and a "temporary" file that imports the text file. The macro may need to switch from one file to the other to move the imported tab from the ...

Automatic Macro
What's the syntax for executing a macro everytime a different cell gets focus ? thanks --- Message posted from http://www.ExcelForum.com/ HI, Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Application.Intersect(Range("C4:C4"), Target) Is Nothing Then runMyMacro End Sub jeff >-----Original Message----- >What's the syntax for executing a macro everytime a different cell gets >focus ? > >thanks > > >--- >Message posted from http://www.ExcelForum.com/ > >. > ...

Saving Personal Macro Workbook
Recently, my Excel 2003 has started prompting me to Save my Personal Macro Workbook every time I close to Excel--even when I don't add anything to it. In fact, right now, my don't have anything at all in my Personal Macro Workbook. I've checked for any rando code in a Shhet or in the Workbook, and I can't find any. Anybody know how I can stop this from happening? tj Did you recently upgrade from a previous version of excel? Did you copy over your older version of your personal.xls file? If yes, then excel likes to recalculate workbooks saved in older versions of exce...

Count totals with date
Hi I need to count the total licences by type used weekly but display the answer monthly. I think it should be something like: =SUMPRODUCT((MONTH($C$2:$C$12)=1)*(YEAR($C$2:$C$12)=2004)) will count the number of date entrys, but I dont know how to put the rest of it together. Can any one help? A B C Licenses Totals Date Web-mail 19392 01/03/2004 POP 19392 01/03/2004 Web-mail 99827 08/03/2004 POP 99827 08/03/2004 Web-mail 11023 15/03/2004 POP 11023 15/03/2004 Web-mail 10067 22/03/2004 POP 10067 22/03/2004 Web-mail 1...

preventing duplicates but not totally blocking
I created this code to pop a box up that will let me know when i have a duplicate number, however, I want to still be able to write a duplicate entry. Private Sub OEM_NUMBER_BeforeUpdate(Cancel As Integer) If DCount("[OEM_NUMBER]", _ "[ATLANTIS]", _ "[OEM_NUMBER] = '" & Me.OEM_NUMBER & "'") Then MsgBox "This oem number already exists!!!" Cancel = True Else 'Do nothing End If End Sub chynewalker wrote: > I created this code to pop a box up that will let me know when i have > a duplicate number...

Run macro from clicking on a cell
Is there a way that you can run a macro from clicking on a specified cell with a hyperlink or function. I can use the shortcut key, but some 'here' say it is not user friendly to have to use a shortcut key. help files haven't helped. -Joe -- jrd269 ------------------------------------------------------------------------ jrd269's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23815 View this thread: http://www.excelforum.com/showthread.php?threadid=376404 jrd269 wrote: > Is there a way that you can run a macro from clicking on a specified > c...

Shift key to disable macros
Hi, I thought that the by holding down teh shift key when opening excel macros would be disabled. However, I have a file that has some code in thhe Workbook_Open() event. When I open Excel holding down the shift key (left one, the right one, or both!!) it still runs the code. Why? TIA, Jarryd Aha!!!! You have to open the file from within Excel while holding down the shift key..... ahhhhhh. Jarryd "Jarryd" <jarryd@community.nospam> wrote in message news:uuJ16RuyIHA.3968@TK2MSFTNGP04.phx.gbl... > Hi, > > I thought that the by holding down teh shift key ...

Credits on SOP Invoices
I was hoping to be able to show credits on SOP orders and invoices. We sell large HVAC compressors and frequently have core exchanges/credits. We also often return parts ordered incorrectly by the customer and then have to send them the right one. We recently upgraded from 8 to 10. I went into the test company to see if it would work, but I just get an error message that says "You can't enter a negative value". I looked in the setup for SOP and did not find anything that would help. Does anyone have any suggestions? Thanks in advance!! This forum is a lifesaver and...

starting Word with an Excel macro
I found this piece of code that opens Word from within an Excel macro Private Sub Open_word_run_build_directory() Dim oWord As Object Dim oDoc As Object Dim wpath As String wpath = ActiveWorkbook.Path Set oWord = CreateObject("Word.Application") oWord.Visible = True Set oDoc = oWord.Documents.Open(wpath & "\build directory V6.doc") End Sub The Word doc ‘build directory V6.doc’ has a VBA macro in an AutoOpen subroutine that executes when it opens. Everything works fine except Excel closes after executing the above routine. ...

How do I get the sum total of all sheets in a workbook?
I have a 52 sheet workbook, all sheets have the same number of columns and data. What do I need to do to arrive at the sum total of all these sheets without printing them all out and adding them up manually? Thanks in advance for your assistance. Ortega, try something like this =SUM(Sheet1:Sheet52!A1) -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "OrtegaGroup" <OrtegaGroup@discussions.microsoft.com> wrote in messa...

macro question :)
hello, folks =) i have a question about macros. i will explain what i'm trying/hoping to be able to do. i want to write a macro that will search for a particular value in a cell. when it finds that cell, i want to be able to copy a cell range with that cell as the upper left and 10 columns and 6 rows below that cell. for example, if i find the value in cell A6, i want to be able to select and copy the range A6:J11. then, that will be pasted into a different worksheet within that file. the last step would be to copy the cell that was found in that search, and paste that elsewhere. the pr...

Annotation and Invoice Dates
Hi, I'm desiring to append note dates and invoice dates from another database into CRM 3.0. According to the Metadata browser this is not supported. Is that true? If so, any other suggestions to preserve the years of account history from our other apps? ...

Macro to copy paste values based on cell input ...
Hi, First of all thanks a lot for your assistance. I did spend some time researching the excel community and trying few codes but I was not able to find modify one to fit my needs, so I am hoping that someone can help figure out why the code I wrote below does not work (I know it is not the best coding possible but if works I am set :-) Any other suggestions are welcome of course :-) Cell A44 value changes (1 to 12) based on user selection (month of a date in another worksheet). =MONTH('Direct Cat_Infl&Perf'!J3) columns B to M are respectivelly, Jan to Dec a...

Separating Transactions from invoices / Accounting
I own a sporting goods store where we do both retail and team business. Right now I dont have an integrated accounting system. Here are my two concerns: 1. Is there a way to invoice from the RMS that allows for the user to get the invoice upon deliver? 2. Is there a way to keep/mark/or some other way establish invoices as team business instead of regular retail business? 3. If it is done through an integrated accounting system is there one package that is better suited than another? Peachtree/Quickbooks/Something Else? Thanks - Jamie 1 & 2. Yes. You do not have to require a ...

Excel VBA Macro executing half-way
Hi, I have a macro which I run regularly and successfully until last couple days when it stopped executing half-way. This also happened to other macros as well. I debugged the macro and it stopped at certain line but gave no error, then started from the beginning of the code. Thank you for any comment. TIA Agustus ...

Totals For An Inside Row Field In a Pivot Table
Hello, Is it possible to get totals for an inside row field in a pivot table? I have a table in layout as follows: Row Fields - Section, Year Column Fields - Rev, Exp, FTE OR: ...........................................Rev...........Exp.............FTE Sect............Year 3425... .....................2000................$$$.........$$$.........## .......................2001.................$$$.........$$$.........## ........................2002................$$$.........$$$.........## 3428... .....................2000................$$$.........$$$.........## .....................

Sort and Group Model(s) w/a Total of Each
Hello, I am using MS-Access 2003. I have constructed a db to take inventory of ALL the desktops in a company. I have identified at least 6 different models (GX1, GX150, GX260, GX270, GX280 and GX620). I have used surveys to identify the User, Dept, Model and current operating system (OS). I have grouped and sorted the User, Dept, Model and OS. What I am asking is, "how-to" do the following: User: Dept: Model: OS: Bozo Bat Cave GX1 Windows 2000 Batman Bat Cave ...

Macro Help Needed
I have a macro to send emails which used to work but now doesn't. Whilst running it within VB it flags an error with this part:- 'Execute the URL (start the email client) ShellExecute 0 &, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus I can't work out why because as far as i can tell nothing has changed. ...

Excel 2000 templates - spreadsheet solutions
I am using OS Windows 2000 Pro - server is Windows 2000, and Excel is verison 2000: when I try to open the invoice template, in the Spreadsheet Solutions tab, I get the following error message: "This workbook contains a type of macro (Microsoft Excel veriosn 4.0 macro)that cannot be disabled nor signed. Therefore, this workbook cannot be opened under High Security level." Any suggestions as to how to fix this problem? Any registry fix? Thanks. Change you security under tools>>security to medium.. -- Regards, Peo Sjoblom "Fabiana Moradkhani" <FMo...

macro
Hi, i need a macro that will copy datas from sheet1 : J5 ;D13 ;D14 ;D15 ;F15 ;E19 ;E20 to Sheet 2 to range A:G. But i need the macro to paste to first empty row that it finds. Every time i run macro, to add the informations to the next empty row. The ideea is to add informations so i can create a small database. Can this be done? Thanks! Maybe this stuff from Debra Dalgleish's site: http://contextures.com/xlForm03.html and maybe... http://contextures.com/xlForm02.html puiuluipui wrote: > > Hi, i need a macro that will copy datas from sheet1 : J5 ;D13 ;D14 ;D15 ;F15 ...

Keep Format After Merging Worksheets Using CopyFromWorksheet Macro
Hello, I have a couple of worksheets that I need to merge into one worksheet which I can do with no problem but how can I keep the existing format from each worksheet in the new merged worksheet? Some of the formats that I have are highlighted cells. Here is the macro that I am using. Thanks in advance. Mark Sub CopyFromWorksheets() Dim wrk As Workbook 'Workbook object - Always good to work withobjectvariables Dim sht As Worksheet 'Object for handling worksheets in loop Dim trg As Worksheet 'Master Worksheet Dim Rng As Range 'Range object Dim colCount As Integer 'C...

create report from Purchasing Invoice Inquiry Zoom window
I'm using GP8.0 and SQLSERVER2K and I'm trying to create a report from Purchasing Invoice Inquiry Zoom window but I'm having trouble understanding the table relationships. Basically I need to see what checks have been paid against invoices. The only report available doesn't give this detail information. Can some please guide me in the right direction to solve this issue? Thanks. Table PM30300 is where you probably want to start....it has the apply information for all fully applied payables checks in the system. If you need info for partially applied checks, that would ...

Totally Totally baffled
My code below is triggered by a UserForm (when it places data into one of the cells in rows 5 - 9) to sort names in the cells. It works great the first and second time it runs but after that it only sorts rows 6 - 9. I am at a total loss as to how to fix this. Any ideas? bSELCTIONCHANGE = False On Error GoTo ErrorHandler Events.Disable_Events Application.ScreenUpdating = False Dim c As Range Dim rowtop As Integer Dim row1 As Integer Dim Row5 As Integer Set c = ActiveCell If ActiveCell.Row < 10 Then rowtop = 4 row1 = 5 Row5 = 9 End If If R...

Protection with macro & paste
Hi I'm using a macro for protecting work sheets while enabling th grouping: Private Sub Worksheet_Activate() With Me .EnableOutlining = True .Protect "password", UserInterfaceOnly:=True End With End Sub But it is not possible to copy-paste data from other sheet to unlocke cells. With normal protection it seems to be OK. Any hints what's happening & how to go around? Thanks PJ -- Message posted from http://www.ExcelForum.com >>But it is not possible to copy-paste data from other sheet to unlocked cells. I can't duplicate this problem. I had no problem...