Capturing worksheet names from one workbook into one worksheet

**MUNECA** · 05-08-2013, 12:37 AM

Hello! I'm soo glad I found this sight. I would appreciate any assistance that can be provided to me to solve this problem: I have one Excel workbook (Excel 2010 version) that has over 50 worksheets and thus 50 worksheet names. I need to capture each of these worksheet names into one worksheet. Is there any easy way to do this? Thank you very much in advance for your time and expertise!

**:) Sixthsense :)** · 05-08-2013, 01:05 AM

Try the below code...

Sub GetAllSheetNamesInNewSheet()
Dim i As Integer

Sheets.Add Sheets(1)
Range("A1").Value = "Sheet Names"

For i = 2 To ActiveWorkbook.Sheets.Count
    Cells(i, "A").Value = Sheets(i).Name
Next i

End Sub

Refer the below link to know how to insert VBA code in Module
http://www.contextures.com/xlvba01.html

**NeedForExcel** · 05-08-2013, 01:10 AM

Use this code -

Sub SheetNames()
    
    Dim SC As Integer
    Dim Counter As Integer
    SC = ThisWorkbook.Worksheets.Count
    
    Sheets("Sheet6").Activate
    
    ThisWorkbook.Sheets("Sheet6").Range("A1").Select
    
    For Counter = 1 To SC
    
        ThisWorkbook.Sheets("Sheet6").Range("A" & Counter) = ThisWorkbook.Sheets(Counter).Name
        
    Next Counter
    
    
End Sub

Change Sheet 6 to what ever sheet you want the name to go to.

Thank You,

Deep

**MUNECA** · 05-08-2013, 01:33 AM

Thank you very very much for helping me. I actually found a macro from an earlier contributor that worked beautifully however I have another question that I would truly be grateful to receive some assistance. I have one excel 2010 workbook that has 50 worksheets. Each of these worksheets contain data in very specific cells. I need to extract into one worksheet the data found in each of the 50 worksheets that is found in the following cells: b3, b4 and h54. Is there a macro that can accomplish this? THANK YOU!!!

**:) Sixthsense :)** · 05-08-2013, 02:05 AM

Where the B3,b4 & h54 cell values needs to be shown in new worksheet? whether in A1,A2 & A3 and next data in A4,A5 & A6 like that?

**MUNECA** · 05-08-2013, 02:35 AM

yes! So my summary worksheet should capture all the date in this fashion:

Column A Column B Column C
5076-7 STAR 11500 E. MCNICHOLS, memphis, tn 78521, MI 48205 $1.90
1474-7TH STREET MARKET 270 SOUTHFIELD ROAD Brooklyn, NY 48229 $3.20
1002-8 MILE FOODLAND 18880 W. 8 MILE RD, olmstead, oh 45333 $274.80

**:) Sixthsense :)** · 05-08-2013, 02:49 AM

May be try this...

Sub GetDataFromAllSheets()
Dim i As Integer

Sheets.Add Sheets(1)

For i = 2 To ActiveWorkbook.Sheets.Count
    With Sheets(i)
        Range("A" & i).Value = .Range("B3").Value
        Range("B" & i).Value = .Range("B4").Value
        Range("C" & i).Value = .Range("H54").Value
    End With
Next i

End Sub