Greetings,
I would like to create a macro that will copy and paste specific values from one workbook into specific cells in another workbook with some complications (for me anyway!). Here is what I have in mind:
Example Source Workbook
Example Receiving Workbook (will be open at time macro is run):
Condition #1: If Column A row Employee # = 1414 AND corresponding Column B cell = Q1 then paste corresponding values from above into the corresponding Employee # (i.e. 1414) workbook and the following cells:
Condition #2: If Column A row Employee # = 1414 AND corresponding Column B cell = Q2 then paste corresponding values from above into the corresponding Employee # (i.e. 1414) workbook and the following cells:
Condition #3: If Column A row Employee # = 1414 AND corresponding Column B cell = Q3 then paste corresponding values from above into the corresponding Employee # (i.e. 1414) workbook and the following cells:
Condition #4: If Column A row Employee # = 1414 AND corresponding Column B cell = Q4 then paste corresponding values from above into the corresponding Employee # (i.e. 1414) workbook and the following cells:
There won't be mixed data from multiple quarters in the Example Source Workbook above on the same worksheet...it will just be rows of data with no special formatting and will be one quarter at a time. There will be 4 worksheets, each representing a quarter of their own. When I run the macro, it can be the same coding that will run on whichever quarter's worksheet I have activated at the time.
Each Employee # in the Example Source Workbook will have a corresponding workbook, each with 4 quarters, that will be open prior to running the macro. When I run the macro, the values for each Employee # in the corresponding row should be transferred to the corresponding workbook and cells as shown in the Example Receiving Workbook above. If the macro is able to go through each Employee # using one button that would be ideal as opposed to having to run a macro for each Employee #. If by chance there was an Employee # in the Example Source Workbook but no open corresponding Excel file with the same Employee # I would like the macro to stop running and provide an error message indicating that has occurred.
This is way above my ability and I hope it's not too complicated to make this happen.
Thanks in advance,
Chad
I would like to create a macro that will copy and paste specific values from one workbook into specific cells in another workbook with some complications (for me anyway!). Here is what I have in mind:
Example Source Workbook
| COLUMN A: EMPLOYEE # | COLUMN B: QUARTER | COLUMN C: KPI 1 | COLUMN D: KPI 2 | COLUMN E: KPI 3 | COLUMN F: KPI 4 |
| 1414 | Q1 | 52 | 69 | 75 | 19 |
| 1414 | Q2 | 65 | 85 | 16 | 74 |
| 1414 | Q3 | 68 | 95 | 12 | 47 |
| 1414 | Q4 | 45 | 82 | 64 | 18 |
Example Receiving Workbook (will be open at time macro is run):
Condition #1: If Column A row Employee # = 1414 AND corresponding Column B cell = Q1 then paste corresponding values from above into the corresponding Employee # (i.e. 1414) workbook and the following cells:
| KPI 1 = CELL B5 | KPI 2 = CELL B7 | KPI 3 = CELL B9 | KPI 4 = CELL B11 |
| 52 | 69 | 75 | 19 |
Condition #2: If Column A row Employee # = 1414 AND corresponding Column B cell = Q2 then paste corresponding values from above into the corresponding Employee # (i.e. 1414) workbook and the following cells:
| KPI 1 = CELL D5 | KPI 2 = CELL D7 | KPI 3 = CELL D9 | KPI 4 = CELL D11 |
| 65 | 85 | 16 | 74 |
Condition #3: If Column A row Employee # = 1414 AND corresponding Column B cell = Q3 then paste corresponding values from above into the corresponding Employee # (i.e. 1414) workbook and the following cells:
| KPI 1 = CELL F5 | KPI 1 = CELL F7 | KPI 1 = CELL F9 | KPI 1 = CELL F11 |
| 68 | 95 | 12 | 47 |
Condition #4: If Column A row Employee # = 1414 AND corresponding Column B cell = Q4 then paste corresponding values from above into the corresponding Employee # (i.e. 1414) workbook and the following cells:
| KPI 1 = CELL H5 | KPI 1 = CELL H7 | KPI 1 = CELL H9 | KPI 1 = CELL H11 |
| 45 | 82 | 64 | 18 |
There won't be mixed data from multiple quarters in the Example Source Workbook above on the same worksheet...it will just be rows of data with no special formatting and will be one quarter at a time. There will be 4 worksheets, each representing a quarter of their own. When I run the macro, it can be the same coding that will run on whichever quarter's worksheet I have activated at the time.
Each Employee # in the Example Source Workbook will have a corresponding workbook, each with 4 quarters, that will be open prior to running the macro. When I run the macro, the values for each Employee # in the corresponding row should be transferred to the corresponding workbook and cells as shown in the Example Receiving Workbook above. If the macro is able to go through each Employee # using one button that would be ideal as opposed to having to run a macro for each Employee #. If by chance there was an Employee # in the Example Source Workbook but no open corresponding Excel file with the same Employee # I would like the macro to stop running and provide an error message indicating that has occurred.
This is way above my ability and I hope it's not too complicated to make this happen.
Thanks in advance,
Chad
