thank you stnkynts...i'm a lot closer than i was before.

when i run the macro, it pastes all the data except for "Full name2" and "Office Location" for the last 3 worksheets. In my real workbook, I have these worksheets named something other than Sheet1, Sheet2, etc. I was thinking if there was a way to name which worksheet I want to copy from to avoid any confusion? Otherwise, this macro worked in the test example.