Code For Moving Data From Row To Template Invoice Format - My Worst Nightmare
Jan 22, 2010
I’m looking to create an invoice-type format from data in a row but only when the client requests it.
So, if there is a Y in column R. i.e. the client wants an invoice format then.
I have been given a “template” format by the team manager that I have to use. Its far from ideal, well for me anyway, for moving things around and I’m struggling to get anywhere with it.
I’m trying to take the data from the row in the source / client worksheet, which will be created via a code from here, and transpose it to a copy of the “template” worksheet which sits within the same workbook. This is made more complicated because in some cases the data for the invoice will be in two or more rows, because there will be a few items on the same invoice, but they will still be on the same worksheet.
The source / client worksheet has data from columns A to T with a header in row 1.
The details will need to be transposed from the relevant row from each column to the “template” worksheet as follows
From column in source / client worksheet To cell in copy template worksheet.
From - To
CB2
AB3
BB5
HB7
IB9
GB11
EB13
FB15
RB19
SB21
QB23
OB25
PB27
This is where it gets messy, or more messy should I say
MB33
JB35
LB37
KB39
TB41
NB45
For each of the six sections M to N above there may be more than one relevant items which will be on rows 3 onwards of the source /client worksheet.
So, ideally I’m guessing based on if there is data in column A of the source / client worksheet then M to N above will need to copied downwards, i.e. below itself on the template worksheet say from column A and B rows 33-45 copied to A and B rows 47-59 until all the data is copied over.
Blinking eck ... this is a nightmare… a real nightmare. This more than one section above is really bad… I’m at a complete loss.
Although if its really not possible they may have to have another template worksheet with the second, third etc items on as I just can’t see this working.
View 9 Replies
ADVERTISEMENT
Mar 22, 2012
I'm looking to create a dynamic invoice that has one main invoice worksheet that references a worksheet with client billing information. Keep in mind that line items will differ between clients, and ideally I'd want to be able to:
Have date autopopulate, included in every line item Select a client, which populates billing info as well as the invoice # nomenclature specific to that client Be able to have client line items specific to the client populate based on the dropdown I select
Overall, no real specific guidelines, just trying to pick some other people's brains about how to approach a dynamic invoicing template. To preface, we have about 60 clients.
View 7 Replies
View Related
Aug 6, 2006
I have a template of an invoice (in excel) but I need it to automatically
generate a different invoice number every time I open it. Such as 001 the
1st time, 002 the 2nd time etc.
I have no knowledge of macros & do not want to uses Acess.
View 13 Replies
View Related
Apr 16, 2006
I have a data base for a stock.
It goes like this:
Date Price
Date1 Price1
Date2 Price2
How can I do an analysis of the:
Worst 12mths performance (like how much is the maximum a stock drop in any rolling 12mths period)
best 12 mths performance
View 4 Replies
View Related
Jul 24, 2007
I have a workbook with 5 Tabs. One of these tabs is "Completed" (for completed work) The other tabs are names of Managers and the tabs contain information about who is doing what work for the Manager and information about it.
What i would like to do is in column F on every sheet is the "status". I would like when the "status" is changed to completed, to have VB code move that entire record to the Completed tab.
I think its possible i just don't know how to do it.
View 14 Replies
View Related
Nov 2, 2011
I use a userform to make sales data entries into a database. The userform contains textboxes for:
Invoice No.Invoice DateGrossVatNet The Invoice No. is the first entry box on the userform and it is important that users enter the correct format (which is alphanumeric).
It is also important that a checking is carried out to ensure that the Invoice Number being entered has not already been used.
I use the following code to ensure that the numbers are entered in the desired format:
Code:
Private Sub txtInv_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If txtInv.Value = vbNullString Then Exit Sub
If (Not UCase(Me.txtInv.Value) Like "ST###") And (Not UCase(Me.txtInv.Value) Like "ST####") Then
MsgBox "Non Valid Invoice Number.â€
Cancel = True
End If
End Sub
And this code is pasted lower down the VB editor window. And it seems to be working fine!
For the checking to ensure that the invoice number being typed into the textbox has not already been used, I use the following two codes combined together:
a) to check for invoice No.:
Code:
check for Invoice Number
If Trim(Me.txtInv.Value) = "" Then
Me.txtInv.SetFocus
MsgBox "Please Enter Invoice No."
Exit Sub
End If
b) And to it I add the following additional code for CHECKING that the no. does not already exist in the database (the invoice nos are located in Column A of the database):
Code:
Dim x
X= Application.Match(Me.txtInv.Value, Columns(1),0)
If Not IsError(x) Then
MsgBox Me.txtInv.Value & "Invoice Number Is Already Used"
Me.txtInv.SetFocus
Cancel = True
Exit Sub
End If
My problem with the second set of codes in (a) & (b) (for the Checking of already existing No.) is that the code allows the user to key in the Invoice No. and go through filling in the other textboxes. It is only after clicking the ‘Add’ button that a msgbox comes up to indicate that the ‘invoice no. is already used’.
I need a tweaking of the codes so that the moment an invoice no. that has already been used is typed into the textbox and the TAB button pressed, a msgbox will show up at that stage to indicate that the ‘Invoice no. is already used’. This will reduce time and avoid filling in the other textboxes and clicking the ‘Add’ button before the checking is done.
View 3 Replies
View Related
Apr 27, 2007
I just wanted a formula to give me the Top 25 product by income and the worst 25. A little more complicated then using the sort button. I'd be looking up the values on H9 as top 25 and have all info from c9 to j9
C9 = Item #
D9 = Model
E9 = Value
f9 = SKU
g9 = Days active
h9 = rev in
i9 = net gain
j9 = avg per day
View 10 Replies
View Related
Jan 28, 2010
My problem is that I have a worksheet tab (RawTimeSheetData) which contains a whole series of week/timecode values for a range of people.
I want to accumulate the hours for an invoice period / job code combination. As an example in the tab InvoicePeriodSummaryTimes cell D6 i want to sum all the hours from RawTimeSheetData where both cells A6 & B6 from InvoicePeriod tab = cells D6 & E6 from the rawdata tab.
View 6 Replies
View Related
Oct 30, 2008
I have an Excel Invoice Template, saved as a template.
I have code that generates a new invoice number each time the template is opened in VBA Editor. The auto numbering system works great! However.... (here comes the sad part)
After I enter all the data I need on the invoice, I want to save a copy of the invoice in a different file folder, which I can no problem. The problem is, if I re-open the saved invoice, we will call it Invoice #100, excel asks me if I want to update, if I say either yes or no, the invoice number will change to Invoice #101. I'm sure you can see how this can cause a major problem if we need to compare information later on, finding the correct invoice would be almost impossible as it would not match the customers invoice number.
What I need to know is:
Is there code I can add to the existing code, to stop the increment on a saved invoice, but not on the original template?
View 9 Replies
View Related
May 6, 2014
I've attached 2 test files, one is the database master file containing the projects (each row represents a project, unique reference number in column A) and the other is the blank template file i'm hoping to export data into and then save down with the naming convention "column A_column B.xlsx"
Kept the test files simple but would need to modify any code to apply to much larger database consisting of many more fields etc.
Master.xlsx‎
Template.xlsx‎
View 9 Replies
View Related
Jan 22, 2013
I have a template invoice in excel. What I want is a macro code that when it is run the open template invoice should be sent to a specific email address !!
View 5 Replies
View Related
Oct 22, 2008
I have recorded a macro to filter data on sheet 'To Invoice' copy the filtered list, and paste on sheet 'Invoice' in C16.
The code just keeps looping (not looping in a code sense, it just seems to keep flickering the screen like its going over & over) until it locks up 5-10 seconds-ish and then I have to re-start Excel.
The range B2:E22 is not always populated, it could possibly be B2:E2 (one row), I dont know how to copy the exact data so I expanded the range to what I think would capture any eventuality....
View 9 Replies
View Related
Feb 14, 2014
I am trying to create a beforesave code within my template. The template has certain fields that are blank and when the end user enters data, I want them to be able to click on save or the blue disk and it saves it to a new location, based on their user and some of the cells within the form. I do not want the template to be saved with the data they input. The data they input is saved only when they click the blue disk and it saves to another location, with their username and certain cells in the form. Then the end user can continue using that same template for the next entry and so on. I have a beforeclose that asks them if they really want to close or not. The only time they would close, or hit the big red X is when they are done with all their entries for the day. Here is my code for beforesave:
[Code] ......
View 10 Replies
View Related
Feb 17, 2008
I have some code and then
ChDir "W:MET Logbook"
ActiveWorkbook.SaveAs Filename:= _
"Micro Evaluation " _
& Format(Date, "(mm-dd) ") & Format(Time, "hh.mm.ss AM/PM") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
And I want this to save my workbook in the different location as a template. Only it saves the workbook as a regular excel workbook instead. Any help with what I can change to save it as a template?
View 9 Replies
View Related
Dec 18, 2012
I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order
Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons.
Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later):
Template file name: template_order.xlsm
Template file location: \servershared emplate
Total list file name: total_list.xlsx
Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10
The column heading for order data is located at A11:N11
The selection to be copied is A12:N550 - But only rows where column A includes data (not empty).
(If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
View 1 Replies
View Related
Jul 17, 2012
So I am trying to make a file that when opened will act as a template does and change the file name on start-up.
Typically, the template changes the file name by appending one number (e.g. "Book Template REV 1.1.xltm' upon opening would change the file name to 'Book Template REV 1.11.xlsx'). What I want to do, however, is to custom the name change so I could use the file 'Book Template REV 1.1.xltm' while having it change the file name upon opening to 'Custom Name 567.xlsx', without a user being notified of the change (since typically when a template opens the file, the user has no notification that the file name has changed ever so slightly).
View 1 Replies
View Related
Aug 8, 2006
I have a census file. All records have a TK number assigned to them. I need to create a new workbook (for each TK number) from a template file and fill in the file with data from all the records belonging to that TK number. I can write this out in English, but I don't know what type of functions/searches/etc. to use to make this work most efficiently.
View 9 Replies
View Related
Apr 21, 2007
Does VBA code created in an Excel file that is saved as a Template (.xlt) work the same when the file is opened as an .xls file?
View 9 Replies
View Related
Mar 21, 2008
I am trying to use the worksheets.add method with the Type specifier pointing to a file location containing a worksheet template.
View 9 Replies
View Related
Dec 11, 2012
We've got a bug in our finance system where it can't handle any transactions that have sales but no related commission. The BI team provides a CSV file separately with this information and the sales team has to manually input it. I know how to create a template that can be uploaded into the system but don't know how to pull the data into the template from the CSV file.
I've created the attached example and what i'd like is a drop down box in cell B1 (template tab) listing all the customer codes in column B on the data tab and then based on your selection all the related transaction lines pull into columns A to F (starting on row 4).
Manual Invoicing Query.xlsx‎
View 1 Replies
View Related
Aug 14, 2014
I am after a VBA code that I can use to populate a fax template based on criteria's
Basically I have records which are being logged throughout the day via a User form and sometimes there may/maynot be a charge. the user decides at the time. The column is named "to be charged" and is filled with either Yes/ No.
I need to be able to select the date to print or Just the same day date and print all faxes' that have "Yes" for charge This way all the information for each fax is populated and the user can just print.
and if possible mark a Colum non the master sheet as printed with a X or something
View 14 Replies
View Related
Apr 3, 2014
I have VBA code the extracts data from a source and puts it into excel. I need to change the format of some of the extracted data so i can use it for a VLookup formula. Reference the attached example. Columns A AND B have the extraced data which are dates. In Columns C and D or E and F I have typed what I would like the data to look like so I can use in a Vloolup formula. Is there VBA or a formula that word change the format of the dates in Columns A and B to look like C and D or E and F. The changed format could be moved to any new cells or rows.
View 1 Replies
View Related
Sep 8, 2009
Data Table including-
List of Identifying Code Numbers for customer invoices
Multiple repetitions of individual Identifying Code Numbers in list
Various data in table range including Various Values of invoices from different dates for each repetion of Identifying Code Number.
- Wish to find largest invoice for each Individual Identifying Code Number in the table without using a pivot table.
i have tried combining Max and Large functions with Vlookups etc.
View 9 Replies
View Related
Sep 19, 2006
I have imported some files in to excel. I have attached the test document to give you an example of exactly what Im working with. Here is what I need to do.
1) On a separate worksheet I need to sort out the data by invoice number (Column E).
In the example I have on the worksheet titled "Main" I have listed what I would like it to look like. The purpose of this is that I don't want any spaces in rows. I just want a long list of invoice numbers and their information (Including the project number & customer on all rows). In long story short I want to erase all the information from A18:A25, and add project number and customer name to all the correlated invoice numbers. The way I think I can do this is that the first 4 digits of the invoice numbers are the same if their in the same project.
View 9 Replies
View Related
Apr 8, 2008
Workbooks.Open "E:Prep2008" & "" & Format(ActiveSheet.Range("C6"), "ddd dd-mm-yy") & ".xls"
is code I use to open a workbook (workbook b) based on the date found at cell c6 of workbook A.
Question ... how do I divert activity to to the newly opened WB B from WB A?
Workbooks?.Select?
View 9 Replies
View Related
Dec 24, 2007
I have 2 Workbooks. One is invoice data extracted from SAP and the other is a workbook I have to send to the client (has about 10 worksheets containing 5-10 different branches in each worksheet). The layout of the one I send to the client can not change so I am trying to import the values form the 1st workbook (SAP) into the 2nd workbook. What I do now is copy the first 4 columns of data for each branch and paste it into the 2nd workbook.
I was playing with a VLookup formula but I don’t think that is the correct formula. I need the second workbook to be populated with all the invoice info (Number, Type, Date, Balance) that appears in the raw data workbook.
=VLOOKUP(H14,'[abcbank raw data sample.xls]Sheet1'!$A:$A+'[abcbank raw data sample.xls]Sheet1'!$A:$E,4)
H14 is the branch number on the client sheet (I cut the column containing the branch # on the raw data and inserted it before column A).
Raw Data From SAP
View 13 Replies
View Related
Apr 7, 2010
The way that I was thinking about doing this would be by creating a template in word and using bookmarks. Then I would use VBA to populate those bookmarks based on the person (publisher in this case) i was invoicing.
I'm having a couple of issues logistically though. I'm not sure how to attach the sample invoice, so i'll do my best to write them out and if i figure out how to attach a doc by the end of this i will include a sample so you see what I'm talking about.
Issue 1: Each Invoice form has the publishers Name and Address listed at the top of the form. The problem is, the information i'm pulling the number of sales (and money we owe them) is from a different spreadsheet every month and wouldn't contain this address information on it as it is an aggregate spreadsheet of ALL publishers numbers. Would it be possible to create an initial template for each publisher and then have the rest of the information be populated with the data from the CSV that has their sale information. I could create a seperate database in excel with each publishers address and name but then it might get difficult to match these up. how to automate that, as we have 100's of publishers and invoices are done every month.
Issue 2: Bookmarks seem like a nice way to use VBA to replace their values with excel data (in theory, i've only read about it and never attemped)... However, in this particular invoice form, I have to create a seperate listing in the description field for each product a publisher sold (including how many they sold and how much money they are owed). Some publishers may have sold 5-10 different products, while some may have only sold 1 or 2. How do i get word to insert a new bookmark (or something) based on the number of products a person has sold.
View 3 Replies
View Related
Feb 26, 2010
I have a worksheet that contains data on products including prices and I want to use that worksheet to fill out an invoice for every sale of those products. I know how to make the list/table dynamic and varify the data to "list", then use VLOOKUP to get the price to appear, but what I'd like to know is how to overcome the problem that will arise when a product's price has to change. Eg. if on 1/1/10 the price of product A rises from £15 to £20, how do I ensure that 2009 invoices do not change when I update/edit the price, but that the current price goes up? So far, the only thing I can think of doing is to create another product, but there are several other bits of data about each product on the worksheet which will not change (weight, height, width, supplier etc), so I don't really want to have to duplicate the unchanging bits and also leaving the productA with the old price in the list may lead to accidentally selecting the old price. What do you think? Is there a way of maybe fixing the price to date or something?
View 9 Replies
View Related
Feb 21, 2013
how to create a copy of my employee attendance template. Is there a way to create a copy of the template by entering an employee name in the "name" cell of the template and it automatically renames the sheet that employee name and also saves or recopies the template for use with the next employee?
View 8 Replies
View Related
Aug 1, 2014
Following macro code is for copying Excel Invoice Data to Excel Database. Also cleans the specified cells after copying. Its working Perfectly.
But there are some issues that should be solved.
Problem 1: It copies empty cells(""), formula based cells and blank drop-down list.
Problem 2: After copying it cleans formulas too. Formulas must be remain their.
View 2 Replies
View Related
