We help IT Professionals succeed at work.
Get Started

Copy data from master list to corresponding fields on various worksheets. Excel Macro

jasocke2 asked
Last Modified: 2012-05-09

Please see attached workbook for example & for more info…

What I have is two workbooks, in one workbook there is a master list of data (In attached spreadsheet this is called “Sheet From another workbook”) and in the other workbook are the ‘blank templates’ there are numerous worksheets but the only ones I am interested in are the ones in between the sheets called ‘Start’ and ‘End’

In between ‘Start’ and ‘End’ sheets are uniquely named sheets (in my example, 1000, 1001, 1002) each sheet is identical apart from the name of the Sheet. Each sheet is numbered in the first column 1-68, this is the unique number on each sheet, and the second column is called ‘Total’ these values can be found in the ‘master list’

In short these templates (1000, 1001, 1002) have to be populated with the data from the other workbook (the data from “Sheet From another workbook”)

In the master list of data is the sheet name in the first column, the ‘unique number’ in the forth column and the ‘total’ in the 5th column.

I need a macro which will (from the ‘master list’ workbook) open the blank templates workbook and for each worksheet in-between the start and end sheet lookup the corresponding data in the master list, so for example:

Master List:

1000 | xx | xx | 1 | 678
(the value 678 would go in the total field next to the unique number 1 on sheet 1000)
1000 | xx | xx | 2 | 900
(the value 900 would go in the total field next to the unique number 2 on sheet 1000)
1000 | xx | xx | 5 | 264
(the value 264 would go in the total field next to the unique number 5 on sheet 1000)
1001 | xx | xx | 1 | 264
(the value 852 would go in the total field next to the unique number 1 on sheet 1001)

How the blank templates sheet would look like.
Unique Number      Total
1                            678
2                            900
5                             264

I know this is a big ask but any help would be most appreciated!


Watch Question
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE