I require VBA Code to automatically sync certain cell data to a seperate excel template

Hello all,

Forum newbie, excel novice - moderate user, I hope I wont be too much of a pain for you.

I will start by saying, I understand that I am likely better off using access for this particular job, however, I have no experience of using access, nor a copy of it, nor the time to change too much of my current system, so ideally would like to find a way to make this work using excel only (hopefully not too big of an ask).

I have created a "Tenancy Information" workbook template and I use a seperate document for each tenant, creating a document titled "Client 1 - Tenant Information", "Client 2 - Tenancy Information", etc ("Client 1" would actually read the clients name, say "J. Smith").

I also have generic excel forms, which collect data from these tenancy info sheets, which I can save as PDF and then forward to my clients. What I would like to do, if possible, is have a macro (activated by clicking a button within the "Tenancy Info" document) that would take the relevant data and open it up in said excel form document. I currently have to open each document and pair the relevant cells, which can prove timely.

To give you a brief example:

Tenant Information Form:

Would need to take the:

FIELD (CELL)

1) Client name (B4)
2) Booked Property (B5)
3) Start Date (E4)
4) End Date (E5)
5) Booking Ref No. (B3)

All of this information would be in the same relevant cells in the "tenant info" document.

Some time ago, I found some code, which would perform a similar exercise, where it takes information from specific cells and opens outlook with an email template, so I am hoping it can do this within excel also.

Any ideas / help would be greatly appreciated.

Thanks

Peter

------------

As an addendum to the original question:

I wonder if there is a way to only have to pair one cell in an excel form template to the "tenancy info" sheet and have the other cells tie in to that one cell so they can collect their data?

I.E something along the lines of:

='[SheetofB1]Main'!$C$6

And when you pair B1 to say, "J.Smith - Tenancy Information" you would get:

='[J.Smith - Tenancy Information.xlsm]Main'!$C$6

and so on?

Would this work and / or be simpler than my original request?