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
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
