Using the find In VBA

Here is the link to the wetransfer documents https://we.tl/t-7d1ZrUWicC

I have included 3 samples of the inventory files, a copy of the invoice template, and a copy of the test ledger template (this file does not have all the needed dealers in it because I was just testing it out to make sure I could do what I wanted to do before I built the entire thing.

You asked for more details of our current data entry process. I will try to give you a run down of our current process.

We start by receiving a truck load of merchandise. We will take the packing slip and enter the quantities into the correct inventory file under the stock field. For example say we got in 4 b070-31's. I would open the Ashley Bedroom File and change the stock field of the B070-31 line, Field "F6" from 1 to 5.

Next we will field calls from dealers who are checking stock and putting in orders. If they wanted one of the B070-31's I would open the Ashley bedroom file, or whatever inventory file was correct, check the available field which is set to auto populate based on the stock field and the on hold fields from column M to XFD. If there is one available I would go to the first available hold column and enter the Dealer, Order Date, estimated pickup date and quantities that they want on order which will take that quantity out of the available column.

When the dealer comes in on the pickup date we would hand write the below invoice, price it and then mark in how much the dealer paid. After it is loaded we would take that invoice and go into the correct inventory file and manually remove the quantities that were purchased from the stock field. Then look to see if the dealer had that item on hold in columns M to XDF and if they got the entire order we would delete that column. If they only received part of the order we would change the quantities on order accordingly.

Next I would manually enter the invoice #, Date, Credit (paid) amount, Debit (Charged) Amount into our ledger book. This is what I am trying to automate with the "Test Ledger" file.

Next I would enter the date, dealer name, payment type (cash, check, or charge), and payment amount into what we call our sales book. This is where we track our cash and check deposits and our credit card amounts that we use to keep our accounts balanced. I have not worked on creating this file or macro yet.

After all this is done I would file the paper invoice in our file drawer. (this would be the saving a PDF version of the file that you talked about earlier. )

That is the process that I am trying to automate as much as possible. I am slowly building the macros separately that I would then try to combine into a single complete button. But I will also need a button that will print a load copy that doesn't complete they invoice so we can hold it until it is loaded.

Sorry this was so long. I wanted to make sure you understood exactly what my overall goal was.

Dear Tim

Re, "... The only problem is that I have 40 files because if one person is in one file adjusting it then I need other people to be able to go in and make adjustments to other files. I split it up by manufacturer and department because of that. It has crossed my mind that there may be a way to pull create a file that pulls information from a single inventory file, but I haven't gotten that far."

YES, I'm very sure it is possible,

To further help you, we need

1. A sample copy of some of the 40-plus Inventory files (the Ashley Bedroom file included)
2. A copy of the Invoice Template and Data Output Table
3. Provide if possible, more details of your current data entry process.

Re, "... I can attach some of the files I have created to show the work I am doing, but again I am drawing a blank on how to do that."

I'm afraid you missed the video I posted in my previous reply, on how to share files and folders using WETRANSFER

Please, try again

https://www.youtube.com/watch?v=NnXsE0SNuCc&t=14s

For other options

Click this link

https://support.office.com/en-us/article/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

Regards

Jeovany

Sorry I misread some of your message. Where would I need to follow you and what does that entail? Doing the work doesn't bother me. What I am mainly trying to do is automate as much of our process as possible, but changing as little of the steps as possible. Yes I know that we will be moving from pen and paper to computer based, but I feel that if I do it right we can still follow mostly the same process that we currently do.

Andreas,

Yes I am definitely a beginner when it comes to macro's, and I am certain that there are things that could be done to clean up my macros. I am trying to move my place of business away from doing everything with pen and paper to more automated processes. I am at the point where anything automated will be a huge time saver. I am also fully aware that there are programs out there that can do this, but those come with a huge cost in most cases. To be honest my first goal was to have an inventory that is on a shared folder so we can pull it from any of the 5 computers in the office instead of having to get up and walk to a different room to check the hand written inventory file. This is completed and working well. The only problem is that I have 40 files because if one person is in one file adjusting it then I need other people to be able to go in and make adjustments to other files. I split it up by manufacturer and department because of that. It has crossed my mind that there may be a way to pull create a file pulls information from a single inventory file, but I haven't gotten that far.

I guess what I am saying in way to many words is yes I probably need some help with everything I am doing. I can attach some of the files I have created to show the work I am doing, but again I am drawing a blank on how to do that.

Jeovany,

1.) I have already done this.

2.) Questioning what I need 2 sheets for. I have created what I call a Ledger sheet to track debits and credits per the sold to field on the invoice.

3.) I have created this already.

4.) saw that this is possible, but it was going to be one of my later steps.

5.) this is currently what I am working on, but it seems like you may be suggesting that I can do more then I thought was possible.

6.) Again, was hoping this was possible, but hadn't got there yet.

I would be happy to attach my current files, but I am drawing a blank on how to do that.