Excel 2007 : Database creation - VB script assistance

Hi Plato,

I'm a big advocate of using the best tool for the job, so normally I would say for a creating a database, use a database application rather than a spreadsheet. However, this might not be best in all cases, consider the following:

Are you creating more than 1 list of data that needs to be linked together?
Are you looking to do reports/number crunching across more than one list?
Will you need to extract certain data from your list/s for mailmerge etc?
Is a lot of the data replicated and do reports depend on the data that is replicated?

If the answer is yes to the above (and this is by no means exhaustive), it is probably better to look to use a database - this allows you to easily link groups of data, report on this and set validation rules for entering data. That said you need to spend quite a bit of time planning and properly implementing a database as they are difficult to use/amend if there are issues once they start being filled.

From your description however, it looks like you are only creating a single list that you need to be able to search. This is fairly straight forward with a bit of VBA code, if you can post a sample of your workbook, I'm happy to have a look and see how we can do what you are looking for.

Hi again,

Kyle, unfortunately I can't post from my work computer (being a work document), but I have put the code below. Hopefully it will be enough, otherwise I will see what I can do.

I haven't yet learn't how to actually write code, so I have cannibalised much of this code from these forums, so please ignore the fact it is probably not very coherent

The bolded part is from what a recorded macro and keeps giving the 1004 error.

Please Login or Register  to view this content.

I figured out how to the copy and paste value through macro recording (in a very round about way). I have it working for one part (where it copies data on the Calculation Sheet to target cells on the Calculation sheet), but now I cannot have it copy data from the Calculation Sheet, to the Sub Cashier Receipt sheet. I have a feeling this is because I haven't defined the variables of the new sheet (?).

If I can get this sorted, I should be able to adjust it to use for creating the database. I think Then it is just the search function, which I have managed to find a simmilar code for which I will try.

Thanks in advance for your help. I'm sure this is fairly entry level stuff, but I cannot figure this out and it is not as easy as I thought to find good documentation on the creation of macro's.

Roy, I grouped them together as it was all related, but suggestion taken on board. Next time Ill make it simpler.

Look forward to your replies.

I'm not really sure what data you're trying to copy from the Calculation Sheet to the Sub Cashier Receipt sheet, the code you highlighted looks to copy from the Sub Cashier Receipt sheet and paste to the active sheet.

You shouldn't really use cut and paste, it's messy and makes things more complex than they are for example, your highlighted code could be rewritten as below:

Please Login or Register  to view this content.

Hi Kyle,

This worked but copied all the data including the formula's I had used as cell links so they were not dynamically updating from the data entry sheet. I experimented a bit more but cannot seem to get it to work.

I have been able to attach a copy of the workbook though so hopefully this will help (I am uploading from a home computer instead of a work one).

My intentions are to have the end user enter data into the Data Entry Sheet.
The "Print Coversheet..." button prints the coversheet as well as adding the relevant data to have a continuous database (on the Mail Register sheet), and temporarily copies relevant data to the Sub Cashier Sheet (SCR).
The "Close" button clears the Data Entry Sheet, then prints the SCR sheets and clears the data from these.

The Search sheet is part two of my plan, but I'll handle that after I manage to get the entry part sorted.

Thanks again.

thanks roy. appreciate your understanding.

I Think this should be your first sub:

Please Login or Register  to view this content.

Hi Kyle,

Thanks for the help, this seem to work for the most part. I was still getting an object error regarding the second value copy

Please Login or Register  to view this content.

(onto the SCR sheet) which I got around by defining the worksheets then sheet name and moving the copy location to the Calculation sheet. Is there any reason why this would have an issue coppying it to another sheet, or do I have to make this sheet active to have it transfer accross??

Anyway, thanks a lot for your help, I'll be able to use all this for my register as well. Much appreciated.

Regards,
Josh

Glad you've got it working.

The only thing I can think of is that you don't have a sheet named "SCR"