data tracking

[sjdavis]

I am trying to use templates with data tracking--invoice and expense. I would like to have more than one database for info. to be put into-ie. if i have more than one client and i want to keep track of my invoices by client. or if i am keeping track of expenses with expense template, I have more than one client that i will have several trips to track. and everything is filed under the Library, can you move the data tracking file to another location in a folder. I know I am probably making things too hard, but can't seem to get things to work the way i need

 

[DonkeyOte]

would suggest you don't use template wizard at all...

you can re-create the effect of a template with data tracking in code - and you could then allow for a variable in terms of which database the data from the "template" gets entered into - by ensuring that the sheets are named by client.

 

[kskinne]

just curious, would you please share your code for mimmicking the template wizard w/ data tracking?

thanks,
kevin

 

[lenze]

What are the fields you are tracking and where do you want each field to be entered (ie What sheet in your workbook)?

 

[DonkeyOte]

Kevin the easiest way to show this (and I have done it for a few people now) is to send you a workbook so you can see the code etc...email me and I will send you something. It's not complicated. I choose not to use the Wizard as it's susceptible to losing the link every now and then which means setting it up all over again which can be a right pain in the a**

 

[DonkeyOte]

Here's the basic code I would use (though obviously you can make the db sheet variable to match a client name or another field on your template)

Points - I have set up a worksheet called "Template" on which there are four fields that I wish to copy to the "Database" sheet.

Sub TEMPLATE_WIZARD()

Application.ScreenUpdating = False

TEMPLATE_SHEET = "Template"
DATABASE_SHEET = "Database"
COUNT_ROW = 2
DATABASE_RECORDS = Sheets(DATABASE_SHEET).Range("A1:A10000")

'To identify the next blank row in the database sheet

For Each DBRECORD In DATABASE_RECORDS

If DBRECORD <> "" Then COUNT_ROW = COUNT_ROW + 1

Next DBRECORD

'To copy the data from the template to the database

Sheets(TEMPLATE_SHEET).Select

'Data Field 1 to database

Range("B1").Copy
Sheets(DATABASE_SHEET).Range("A" & COUNT_ROW).PasteSpecial xlPasteValues

'Data Field 2 to database

Range("B3").Copy
Sheets(DATABASE_SHEET).Range("B" & COUNT_ROW).PasteSpecial xlPasteValues

'Data Field 3 to database

Range("B5").Copy
Sheets(DATABASE_SHEET).Range("C" & COUNT_ROW).PasteSpecial xlPasteValues

'Data Field 4 to database

Range("B7").Copy
Sheets(DATABASE_SHEET).Range("D" & COUNT_ROW).PasteSpecial xlPasteValues

'To sort the database on basis of say date - column D in Database

Sheets(DATABASE_SHEET).Select
Range("A1:D10000").Select
Selection.Sort KEY1:=Sheets(DATABASE_SHEET).Range("D1"), ORDER1:=xlAscending, HEADER:=xlYes, ORDERCUSTOM:=1, MatchCase:=False, Orientation:=xlTopToBottom

Sheets(TEMPLATE_SHEET).Select
Range("C1").Select

ActiveWorkbook.Save

End Sub

 

[kskinne]

LASW10:

thanks for the code - very useful

regards,
kevin

 

[DonnieD@York]

I am working on a project that is very similar to this. I am trying to use the code that had been posted earlier. But I need to change parts of the code to make it fit. But since I know very little vba, I am having difficulty. This example uses a template and a database that are two sheets in one workbook. How would the code change if the database was a sheet on a separate workbook?