VBA macro - Index match then copy/paste into rows where the values reside

Status
Not open for further replies.

Yooks

New Member
Joined
Apr 2, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi peeps,

Sorry for the botched subject, I have no idea how to word the macro I am seeking.

Quick Summary:
I want a macro to copy the data from all rows in one sheet, then paste the data from​
those rows into a "MasterSheet" but in specific rows where the employee number resides for this example.​
I know I could use a formula to complete such task, however, due to the fast paced environment, rows and value gets changed or deleted every so often.
So I want to keep hard values in the master sheet.


Visual example:

I want to copy the attendance data for employees 3167, 5001, 8538
In this case they're all together.

EXCEL_TIixY2G1FO.png


Now we head over to the master sheet, these employees are all on different rows.
Therefore, I would need a way to paste the attendance data into the row which the employee resides.

EXCEL_Fy5sjmWxnQ.png


Thanks in advanced to anyone willing to tackle my issue. I appreciate it!

Disclaimer: The data in the visual examples are examples only and do not contain any real employee info.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

JG2021

New Member
Joined
Apr 2, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello,

You could use a simple index match for this.

=index( *the information you want to bring over*, match( *the Oracle column in the master sheet*, *the Oracle column in the attendance sheet*,0))

If the information is always changing but in the same column each time you can use the entire column so instead of $A$1:$A$12 you would just use $A:$A that way any information that changes will not effect the formula at all.

Does that make sense or did I read that wrong?
 

Yooks

New Member
Joined
Apr 2, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hello,

You could use a simple index match for this.

=index( *the information you want to bring over*, match( *the Oracle column in the master sheet*, *the Oracle column in the attendance sheet*,0))

If the information is always changing but in the same column each time you can use the entire column so instead of $A$1:$A$12 you would just use $A:$A that way any information that changes will not effect the formula at all.

Does that make sense or did I read that wrong?

Hi JG2021,

I should have elaborated more.

The reason why I cannot use the index match formula is because those 3 employees could be deleted from the original sheet but appear in sheet2 for example.

Therefore the value in April would be deleted in the mastersheet once that employee gets moved to a different sheet or deleted entirely.
 

JG2021

New Member
Joined
Apr 2, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Could you lock the cells so that no one could delete the names?
 

Yooks

New Member
Joined
Apr 2, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Could you lock the cells so that no one could delete the names?

For extra context.

The sheets are teams. So for example, Team1, Team2, Team 3...
So Cathi could be in Team1 then next month could be in Team2.

I could get every team leader just to input data into the mastersheet. However, I want to make this local sheet very user friendly and efficient.

So a team leader could login and log the attendance data quickly just for their team. And then it would in a sense upload to the master file.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Duplicate to: [VBA] Copy/Paste different values based on date

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,129,366
Messages
5,635,847
Members
416,886
Latest member
coreyalaurence37

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