Macro that copies and pastes value from one cell, if another cell contains data

**defensorfedei** · 08-02-2012, 04:22 PM

Hi folks,

I have searched several forums (including this one) for two days and have yet to find what I am looking for exactly.

I have a workbook (which I will attach) that contains 2 worksheets. Worksheet "MasterList" has 2 columns. One contains employee names and the other contains their start times on a particular day. The employee names on this sheet comprise the named range (All_Staff). I would like to have a macro that examines the cell next to "All_Staff" and if it contains a value (<> Empty) then copy the employee name and paste it into the named range (Sunday_People) on sheet "Availability". Ideally this would loop through every name in the "All_Staff" range and insert the name into the "Sunday_People" range.

I have found the following links which seem similar to what I am after, but are a bit confusing.

http://www.excelforum.com/excel-prog...ther-data.html
http://www.excelforum.com/excel-prog...ther-cell.html

Here is the example workbook:Employees.xlsx

Any help would be greatly appreciated. I am new to VBA, and this one is causing me to stumble a bit.

Thanks in advance.

**nilem** · 08-02-2012, 04:34 PM

Hi Defensorfedei, try it

Please Login or Register  to view this content.

**defensorfedei** · 08-02-2012, 04:56 PM

Well,

This works great. Would you be kind enough to explain this syntax to me, so that when I need to edit this to suit my needs, I will better understand what I am doing. Also, how would this work if I wanted to insert within the range "Sunday_People" to keep it dynamic?

Again, thank you very much for this code, I just worry that somehow it is too simple, and will lead to errors if applied to a larger list.

UPDATE: If the list of names made is larger, the values that are pasted go outside of "Sunday_People" range.

**defensorfedei** · 08-02-2012, 11:54 PM

I finally figured out how this works. I was able to make the destination range dynamic by adding the range to a table. Would anyone be able to offer alternative code, just so that I might learn a little something extra.

Thanks again for the help I have received thus far.