Using the find In VBA

Also is there any way you can explain to me why this works.

'Set Variables

Dim Wb As Workbook

Dim Ws As Worksheet

Set Wb = ThisWorkbook 

Set Ws = Wb.ActiveSheet 

'Add Invoice Number

Dim Increase As Range 

Workbooks.Open "\\TIMS-COMPUTER\Users\furnd\POS System\InvoiceNumber.xlsm" 

    Workbooks("InvoiceNumber.xlsm").Worksheets("Sheet1").Range("A1:A1").Copy 

        Wb.Worksheets("Invoice").Range("C2").PasteSpecial xlPasteValues 

            Application.CutCopyMode = False 

    Set Increase = Workbooks("InvoiceNumber.xlsm").Worksheets("Sheet1").Range("A1:A1") 

    Increase = Increase + 1 

    Workbooks("InvoiceNumber.xlsm").Close SaveChanges:=True

BUT

if I change the line

Wb.Worksheets("Invoice").Range("C2").PasteSpecial xlPasteValues

to

Wb.Ws.Range("C2").PasteSpecial xlPasteValues

I get an "object doesn't support this property of method" error

re:

if I change the line

Wb.Worksheets("Invoice").Range("C2").PasteSpecial xlPasteValues

to

Wb.Ws.Range("C2").PasteSpecial xlPasteValues

I get an "object doesn't support this property of method" error

because

Set Ws = Wb.ActiveSheet

so

Wb.Ws.

may report error!

Then my question is this. If I am going to be using the invoice file from multiple computers, since I have a couple people in my office, won't I have to have code to open the Test Pricing and Stock for invoice.xlsm

do you mean multi users share invoice files or data?

in one office or through internet?

re:Otherwise, wouldn't the person creating the invoice have to manually open the file so the code can check it?

if too manay datas and people in different places, time to consider web plus database to manage whole things.

I am still going over your code. Sorry it is taking me a while. Jeovany and I met over video chat and he gave me so options that can be done with power query that I am looking into as well.

Well, I don't know what you discussed with Jeovany, but for the purposes of this thread IMHO there is no way to bring about a solution with Power Query.

Why?

a) With Power Query you cannot write data to another file, you can only read data.
That means you cannot transport any data from "Invoive.xlsm" to "Stock.xlsm" using PQ, which was your original concern!?

b) If you implement PQ in the "Stock.xlsm" it is theoretically possible to import the data from the "Invoive.xlsm" and subtract it from the existing stocks.
However, this is not trivial and requires special preparations.
In addition, no error handling is possible in the process, i.e. an individual error message depending on the situation is impossible.
Furthermore, you can only have ONE invoice file AND it must be the same for ALL users. The background is that the full path must be given in the query.
The fundamental problem, however, is that the query in "Stock.xlsm" can only be executed if the file is opened by a user. And then the file is locked for ALL other users.

I also favor solutions with Power Query, but IMHO it is not appropriate here and just a more complicated way to achieve the goal.
Since Jeovany suggested it to you, please ask him for an example. Then you can test if it works for you.

To my code:

Sit back and let's reverse the direction of view:

I have 2 Excel files open on my computer, in the current one I have numbers in an "ID" column and a number in the "Values" column.
In the other file I also have an "ID" column and an "Stock" column.
Now write me a code that subtracts the number under "Values" from "Stock" for the matching numbers under "ID".

You think this is impossible?

You can write code that does the job, no matter what the files look like or where the columns really are.
This is how the code works I gave to you.

Please don't try to change the code.
In order to make adjustments here, you need some experience of what an object is and how it works in Excel.
In addition, it is not necessary... except for some special circumstances where my code throws an error and you don't want to see it.

The decisive point is to test whether all invoice data is always correctly transferred to the stock.
Only then can you implement additional code such as how "Stock.xlsm" is opened safely and reliably and also display individual messages in the event of possible errors.

Open the "Stock.xlsm" should not be done inside the existing code, but outside. That means you write another routine that checks / opens "Stock.xlsm", then calls my code and pass the workbook or worksheet variable as argument and then saves and closes "Stock.xlsm".
The invoice can only be archived if this has worked without errors. That's the safe way.

Andreas.

Thank you for meeting with me on Tuesday. I have discussed some of your ideas with my boss and he is very intrigued by your idea to have incoming and out going fields with an initial stock and a current stock. I feel like maybe I didn't pay as much attention to that as I should have. Now I am having some issues with how I would go about programming that. Would it be possible for me to see those files and see if I can work my way through them or possibly meet over video chat to talk about them?