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

--
Message posted from http://www.ExcelForum.com

Any help

--
Message posted from http://www.ExcelForum.com

From your description I think this is what you are after


Sub ffffff()
Dim i As Integer ' sheet count
Dim c As Range
Dim rC As Long ' row copy from
Dim rP As Long ' row paste to

' loop through each sheet except 1st
For i = 2 To Workbook.Sheets.Count Step 1
' get last row used in column a
r = Sheets(i).Range("A" & Rows.Count).End(xlUp).Row
' loop through each used cell in column a
For Each c In Sheets(i).Range("a1:a" & r)
' get next free cell in column a on 1st sheet
rP = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row + 1
'populate a cell on 1st sheet with data from a cell on
' another sheet & pad data with sheet name
Sheets(1).Cells(rP, "A").Value = c.Value & " (" & i.Name & ")"
Next c
Next iWs

End Su

--
Message posted from http://www.ExcelForum.com

I will try it. I appreciate your time and knowledge

--
Message posted from http://www.ExcelForum.com