data tracking

sjdavis

New Member
Joined
Mar 4, 2002
Messages
2
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
just curious, would you please share your code for mimmicking the template wizard w/ data tracking?

thanks,
kevin
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
What are the fields you are tracking and where do you want each field to be entered (ie What sheet in your workbook)?
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
LASW10:

thanks for the code - very useful

regards,
kevin
 

DonnieD@York

New Member
Joined
May 6, 2004
Messages
2
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?
 

Forum statistics

Threads
1,144,292
Messages
5,723,540
Members
422,502
Latest member
barakgahtan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top