Copying data from different sheets

[darin]

I need a lot of help with the following: I need some help moving
column of data from different sheets to my front sheet. I know how t
move data but I need to throw a twist into copying data. I want to cop
data from different sheets and put it under the data from the las
page. I would also like to put the sheet name after the data.

Sheet 2
Cell = PVT Smith
Cell = SPC Joe

Sheet 3
Cell = PVT Jones

Sheet 4
Cell - SGT Able

Example of what I want on Sheet 1:

1 PVT Smith (Sheet 2)
2 SPC Joe (Sheet 2)
3 PVT Jones (Sheet 3)
4 SGT Able (Sheet4)
5


Here is a better way of understanding what I want to do:
If I understand you correctly, you wish to scan 5 columns on 5 sheet
and colsolidate all the cells that contain data in those ranges int
one column on your main sheet........I assume one below the other unti
all is done, rather than any sort of concatenation to get them all int
one column. And,
in doing so, you wish to identify each entry with it's source
sheet........

I have no idea how to write a macro so break it down "dummy" style i
possible. This will help me account for soldiers in my unit.

Thanks,
Dari

 

[darin]

Any help