CODEWITHSUNDEEP
excelvba
Robert Anthony Svec
Robert Anthony Svec

Reputation: 35

How to copy data from one excel workbook to another automatically?

I have 2 excel templates. Template 1 is what my clients fill out and Template 2 is what I fill out. Template 1 is more straightforward and user friendly while template 2 has extra fields that the client doesn't need. Right now I copy everything by either reference formulas or just copy and paste. I am wondering if there is a way to copy the data into the right columns automatically. So when a new client fills out the form I can just link it to template 2 and all the data is filled in.

For example:

Workbook 1 (Workbook I get from the client that I want to copy FROM)

Company     Item      Price  
Company A   Item 1    $10      
Company B   Item 2    $15  
Company C   Item 3    $20

Workbook 2 (My workbook that I want to copy TO)

Item     My Cost   My Price   Company     
Item 1   $10                  Company A
Item 2   $15                  Company B
Item 3   $20                  Company C

Upvotes: 0

Views: 130

Answers (1)

Jonathan Musty
Jonathan Musty

Reputation: 11

You can have a look into "merge and compare workbooks". You would need to set up shared workbook and create copies of this for distribution. There are lots of websites that will explain how to do this much better than I can. I would suggest you google this and have a go. If it looks suitable for what you are after, then I would sugest you also lock certain cells so data can only be entered where you want, and also use Data Validation in the data ribbon of excel to restrict the data type being entered, for example, only allow numbers and not text.

Upvotes: 1

Related Questions