VBA Code for a Database using multiple worksheets

[mohorter]

I am trying to create a database that will distribute data to 4 different work sheets with buttons for each set of data.
I had it figured out for one set of data for the same worksheet but can't figure out how to get it to work with multiple sheets.

Before Audit - is the sheet that all the data would be input into. Data from here would go to the other 4 sheets in the file

I also created a clear button, save button, and delete button for each section. (OOO section buttons haven't been created yet)
I had them working just fine for just the one worksheet but with the other sheets they don't work
I've created 4 sets of macros for each worksheet as well.

Not sure what the code for VBA would be to have it pull data and insert data on different worksheets. I've searched the internet and no avail.

I have included a copy of the file i am working on

Here is an example of the code for the FB page

Please Login or Register  to view this content.

Any help would be greatly appreciated

Thanks,

Adam

[BanginMyHeadOnMyDesk]

Your attachment doesn't have any of the macros that your buttons fire so we cannot see the ones that work nor diagnose why the other ones don't work.

However, if you have one section working then use the same approach with the others but change the sheet name of where the data is to be moved to along with cell references etc.

Beth.

[mohorter]

Updated the original post with the code that I am working with for one of the pages the FB page

[BanginMyHeadOnMyDesk]

So you want range D18:P18 from the first sheet to copy to the next available row on the third sheet?
And D25:P25 to go to the next available row on the fourth sheet?

Or is there more to it than that?

Beth.

[BanginMyHeadOnMyDesk]

Does "Load" retrieve a submitted record from the relevant datasheet to the 'Before Audit' sheet?

If so is retrieving simply to view it or could you amend and need to resubmit the changes?

And does "Delete" remove a record from the relevant data sheet?

Beth.

[mohorter]

Thanks for your help Beth
I apologize for making it more complicated than it probably is.
The VBA code i was using came from a video i saw online for a single sheet. But since i have four different entries that i want to keep track of, i have to split the items into four different sheets. I tried it all on one worksheet, but when i would delete an entry, it would delete the line in excel and screw up my code. It works great if you didn't have to change any data, but in my hotel i may have to add or delete items. If i could figure out what one of the sheets would look like for coding i could do the rest. My VBA is newbie at the moment, teaching myself little by little.

The first worksheet is my input sheet "Before Audit". This will be the only visible sheet my auditors will be able to see. I want my auditors to enter the data on this sheet instead of multiple sheets as i have now and have the data go to their respective sheet with them not seeing it. so i want to be able to clear the cells, save the cells, and delete entries if needed, and also edit the cells if needed as well.

Food & Beverage will go to "Before Audit FB" worksheet
Vouchers will go to "Before Audit Vouchers" worksheet
Employee will go to "Before Audit Employee" worksheet
Out of Order will go to "Before Audit OOO" worksheet

"Before Audit" worksheet (my input sheet)
Cells (E4,F7,F8,F9,F10,F11,F12,F13,I7,I8,I9,I10,I11,I13,I14,L7,L8,L9,L10,L11,L12) will transfer to "Before Audit FB" worksheet and be placed in cells (D8:X8)

Next section of "Before Audit" worksheet
Cells (D18:P18) will transfer to "Before Audit Vouchers" worksheet and be placed in cells (D8:P8)

Next section of "Before Audit" worksheet
Cells (D25:P25) will transfer to "Before Audit Employee" worksheet and be placed in cells (D8:P8)

And same concept for "Before Audit OOO"
Cells (D33:F33) will transfer to "Before Audit OOO" worksheet and be placed in cells (D8:F8)

Any help would be greatly appreciated.

Adam

[mohorter]

Does "Load" retrieve a submitted record from the relevant datasheet to the 'Before Audit' sheet?

If so is retrieving simply to view it or could you amend and need to resubmit the changes?

And does "Delete" remove a record from the relevant data sheet?

Beth.

Load does retrieve a record and puts it in the "Before Audit" sheet, and i would like to view, and edit, and save, or delete if needed

[BanginMyHeadOnMyDesk]

OK, and the stuff in columns A & B is just your current attempt at retrieving/deleting? So can be ignored with a different method?

Beth.

[mohorter]

OK, and the stuff in columns A & B is just your current attempt at retrieving/deleting? So can be ignored with a different method?

Beth.

Yes that was with the previous attempt. That can be deleted if needed.

[BanginMyHeadOnMyDesk]

One more question then I'll amend it for you.

You'll need some kind of identifier. Can date be used for that? i.e. there will be only one instance of each date on any given data sheet? Or could multiple rows have the same date?

Beth.

[mohorter]

One more question then I'll amend it for you.

You'll need some kind of identifier. Can date be used for that? i.e. there will be only one instance of each date on any given data sheet? Or could multiple rows have the same date?

Beth.

The FB page is the only one where the Date would only appear once a day. The other pages vouchers, employee, and ooo the date would appear multiple times since there may be multiple instances of for that same day.

[mohorter]

I updated the attachment with some data to give you an idea.

For the instance data we can use the date for the FB sheet. For the Vouchers and Employee we can use the check #.
For the OOO sheet i'm not to sure since there would be multiple rooms for each day that would be out of order.

[BanginMyHeadOnMyDesk]

Hi Adam,

Apologies for the delay. I didn't get forum notification to say you'd replied...

In the attached I've rewritten the code for FB and Vouchers.
It will clear any data recorded on the first sheet if you click the "New" buttons.
It will retrieve stored records (if it can find a match) when you use the "Load" button.
The "Save" button will add the details to the bottom of the relevant list if it cannot find an existing record with the same date/check # and will give you the option to update it if it does find a match.
The "Delete" button will delete a matching record from the relevant list based on date / check #.

You should be able to easily adapt the Voucher code to do the same for the Employee section as it will simply be a case of changing the sheet name and row numbers.

For the OoO section I feel you will need to use a combination of the date and the room number.
If that would work for you and all the above stuff works for you then I can help with the OoO code too if you need.

Hope it's helpful.

Beth.

[mohorter]

Hi Adam,

Apologies for the delay. I didn't get forum notification to say you'd replied...

In the attached I've rewritten the code for FB and Vouchers.
It will clear any data recorded on the first sheet if you click the "New" buttons.
It will retrieve stored records (if it can find a match) when you use the "Load" button.
The "Save" button will add the details to the bottom of the relevant list if it cannot find an existing record with the same date/check # and will give you the option to update it if it does find a match.
The "Delete" button will delete a matching record from the relevant list based on date / check #.

You should be able to easily adapt the Voucher code to do the same for the Employee section as it will simply be a case of changing the sheet name and row numbers.

For the OoO section I feel you will need to use a combination of the date and the room number.
If that would work for you and all the above stuff works for you then I can help with the OoO code too if you need.

Hope it's helpful.

Beth.

I'm excited to see what you did but the attachment is the wrong file, lol

Adam

[BanginMyHeadOnMyDesk]

OOops, sorry! Having one of those days....

Try this one *fingers crossed*

Beth.

[mohorter]

Beth,

This is awesome. Works perfectly to what I want. I have more ideas for this project that i'm working on. I hope to be able to use your knowledge in the future, but i'm going to try to figure it out first.
Much appreciated!!!

Adam

[BanginMyHeadOnMyDesk]

No problem at all, Adam. Happy I could help you along with it.

Good luck with the project and please let us know if we can help more.

Have a good day.

Beth.