Making 2 seperate Excel workbooks talk to one another

[Irishmcclainish]

Hello all,

I'm wondering if it's possible to make 2 separate excel workbooks to talk to one another. For instance if I have a credit card receipt in one workbook and a separate workbook for projects- am I able to make the first spreadsheet talk to the second spreadsheet and add in the correct information without having to retype everything?

Thank you in advance, just trying to work smarter, not harder!

 

[mumps]

I think I understand what you want to do. However, VBA is very "picky". It needs exact cell, row, column and sheet references. So if you put "spence" into the client name column, you need a sheet named "spence" in the tracker workbook. Also, the column headers have to be the same. If you look at your current headers, only one (Date) matches so I know that the "Date" from Receipts goes to "Date" in the tracker.

In the Receipts sheets you have: Date Payable to Amount CC # Client
In the Tracker sheet you have: Date Check No Description Income (+) Labor (-) Materials (-) Balance

I don't know where Payable to, Amount, CC # and Client go because there is no corresponding column in the tracker with the same name. That wouldn't be a problem. However, I would need to know that "Payable to" goes to the "Description" column, "Amount" goes to the "Income (+)" column, etc.
The other issue would be if the tracker workbook is already open or if the macro has to open it. If you enter "spence", that tracker file needs to be opened. If you enter "Smith", that tracker file needs to be opened. So if you are going to enter many different clients, all the corresponding tracker files need to be opened. If the macro has to open them, then the tracker file names would also need to be named based on the client name. So the tracker for "spence" would be named "spence.xlsx". I hope this makes sense. Please let me know how you want to proceed.

 

[Irishmcclainish]

With that being said is it safe to assume both spreadsheets have to be on the same computer? or maybe even network? I can change to make them exact- I just thought the same columns had to be in use- changing the cc receipt workbook isn't a big deal at all. I'll get that changed and send it back through the onedrive.com.

Thank you for your help and patience, I'm learning...and really appreciate your explaining!!

 

[mumps]

You are most welcome. It would be easier if the files were on the same computer. If the macro needs to open the tracker files, then I would need to know the full path to the folder containing the files.

 

[Irishmcclainish]

Will all of this still work if I put both spreadsheets on an external harddrive? Just wanted to make sure before I send over the link for the revised spreadsheets!

 

[mumps]

It should work. The file path would then refer to the external hard drive.

 

[Irishmcclainish]

Good morning,

Ok so, I was able to make the spreadsheets match. here is a new link: https://1drv.ms/f/s!Av5ejM0HttKShT_SyXXGhnfN2jaX

 

[mumps]

We're getting closer. The sheet names in the New Template have the client name and a number which is OK. However, to make things easy, the file name would have to be the same as the sheet name. So you have a sheet named "Lawrence - 8544". The corresponding tracker would have to be named "Lawrence - 8544.xlsx". Would this be possible? Also, each sheet in the New Template file would need to have another column, column H, with the header "Done". What would happen is that the client would enter the data for columns A to G and when finished putting the data in that row, the client would then enter "Done" in column H. The macro would open the tracker file for that client and copy the data for the "Done" row to the tracker. How does that sound? I would still need the full path to the folder containing the tracker files.