VBA - Update data from different worksheets to a single Master Sheet (Responsibility Matrix)

Yazzay

New Member
Joined
Jul 22, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hello everyone,

this is my first time posting and seeking help here, I would appreciate any help.

I will try to explain my situation, you will also find an attached document to help explain.

I am working in business development and wish to establish a responsibilities and financial authorities matrix. That means, a document that consist of all documents and policies and the corresponding actions in these documents, and list the responsible people in each specific process.

My attached document consists of:

- "Marketing" department sheet - one of many departments; the data here will be manually entered by user
- "Finance" department sheet - one of many departments; the data here will be manually entered by user
- "Financial Authorities Master" sheet - where I would like to combine data from the different departments sheets
- In reality there will be more sheets for each department

What I wish to do is, in the "Financial Authorities Master" sheet, I would like to collect all the columns "Document Title", "Document Reference", "Action", as well as some (Not all) departments' job titles, with there corresponding cells, from all the different department sheets but ONLY if there is a "Yes" in the "Financial Authority" column.

Example, in the "Finance" department sheet, there are 5 rows, but only 2 contain a Yes in the Financial Authority column (text in red), I would like to copy the corresponding cells in the columns "Document Title", "Document Reference", "Action", "Finance Department Head" and "Finance Job Title 1" and copy those cells to the "Financial Authorities Master" sheet in the corresponding columns.

I would like to do this for all the departments sheets that I will have, copy the rows that have a Yes in the "Financial Authority Column" and paste in the "Financial Authorities Master" (Not all the cells in those rows, always the first 3 columns and some job titles, usually department heads and 1 or 2 other job titles"

In more details, I want a VBA code that will check each sheet, it will check the "Financial Authority Column", if there is a yes, it will go the the financial master sheet and search for what column titles are there, then go back to the department sheet and copy the corresponding cells, then go to the financial master sheet and search for the first empty row and paste the cells, whenever I later add new rows in whatever sheet, I want the code to keep checking and updating the new data in the master sheet

Notes:
- This Excel document will be for all the different departments in the organization, so there will be more sheets added for the other departments
- The number of job titles columns in each department sheet is different (the number of columns in the finance sheet is different that the one in the marketing sheet)
- The number of job titles for each department in the "Financial Authorities Master" is different than in each separate department sheet(in the finance sheet there are 4 columns for the titles including the department head, in the master sheet there are only 2)

Attached Images:
- Picture of the Marketing Department sheet
- Picture of the Finance Department sheet
- Picture of the Financial Authorities Master sheet (empty)
- Picture of the Financial Authorities Master sheet (filled, how I would want the final product to be)

I hope I explained the problem well.

Am I wishing for something impossible? I don't know, maybe I am
 

Attachments

  • Finance Sheet.PNG
    Finance Sheet.PNG
    26 KB · Views: 7
  • Financial Master Sheet Empty.PNG
    Financial Master Sheet Empty.PNG
    20.9 KB · Views: 6
  • Financial Master Sheet Filled.PNG
    Financial Master Sheet Filled.PNG
    27.9 KB · Views: 6
  • Marketing Sheet.PNG
    Marketing Sheet.PNG
    28.8 KB · Views: 6

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Yazzay

New Member
Joined
Jul 22, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Another picture that might be helpful
 

Attachments

  • Sheets.PNG
    Sheets.PNG
    2.5 KB · Views: 4
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,593
Messages
5,838,257
Members
430,536
Latest member
Manoj Gaidhankar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top