View Full Version : Solved: Combining 1 Cell from sheets into List

04-21-2010, 11:20 AM
Not sure where to even start so i was going to ask if anyone has a macro that could help me...

I have about 1000 sheets of information in 1 file. I need to be able to take the Information from cell D-8 which is a fax number from every sheet and create a separate sheet with a list of these fax numbers.

Is there something out there that could do this?

Just to reiterate i wish to be able to hit the macro and turn 1000 Sheets of individual information, be able to pull just the fax number off at Cell D-8, and have a separate sheet, (say sheet 1001) that has a Complete list of 1-1000 sheets fax numbers.

That way I do not have to go through individually and write them all out...:(

If anyone can plug a macro in here for me to copy, I would be eternally grateful, Thank you.

04-21-2010, 12:42 PM
Sub JustTheFax()
Dim st As Worksheet, i As Long, iShCnt As Long

'delete "JustTheFax" if it exists
On Error Resume Next
Application.DisplayAlerts = False
On Error GoTo 0
Application.DisplayAlerts = True
'get the sheet count
iShCnt = Sheets.Count
'create "JustTheFax" sheet
Worksheets.Add after:=Sheets(iShCnt)
ActiveSheet.Name = "JustTheFax"
' grab the fax
For i = 1 To iShCnt
Sheets(i).Range("D8").Copy Destination:=Sheets("JustTheFax").Cells(i, 1)
End Sub

04-21-2010, 01:33 PM
Solved and thank you so much