Create list of worksheets and cell values

[Guest]

I would like to create a program that will insert a new worksheet that
contains one column for the worksheet name, the value of cell M50 and value
of cell N50. So I will have three columns with multiple rows. The number of
rows will equal the number of worksheets in my workbook. I grabbed this code
from this board (thank you) and tried to modify it but it doesn't work, any
idea's?

Sub GetCellFromWrksheets()
Dim wsNew As Worksheet, wsTemp As Worksheet
Dim r As Range
Dim pdp As String
Dim pmm As String
Set wsNew = Sheets.Add ' add a new worksheet
wsNew.Name = "All_Sheets" ' named "All_Sheets"
Set r = wsNew.Range("A1") ' cell to place the name in
For Each ws In ActiveWorkbook.Sheets 'loop through all worksheets
r = ws.Name ' put the name of the worksheet in the new sheet
pdp = Range("N50")
pmm = Range("M50")
'
'QteNum = Range("B6")
Set r = r.Offset(1, 0) ' move down one cell
Set pdp = pdp.Offset(1, 1)
Set pmm = pmm.Offset(1, 2)
Next ws
' clean up
Set ws = Nothing
Set r = Nothing
Set wsNew = Nothing
End Sub

 

[Ken]

It seems like there is a lot more to the code you selected than the
description you provided of what you want to do. I think the code
below will accomplish what you described.

Sub GetCellFromWrksheets()
Dim wsNew As Worksheet
Set wsNew = Sheets.Add ' add a new worksheet
wsNew.Name = "All_Sheets"

i = 1

For Each ws In ActiveWorkbook.Sheets
Worksheets("All_Sheets").Cells(i, 1).Value = ws.Name
Worksheets("All_Sheets").Cells(i, 2).Value = ws.Cells(50, 13).Value
Worksheets("All_Sheets").Cells(i, 3).Value = ws.Cells(50, 14).Value
i = i + 1
Next ws

End Sub

I hope this helps.

Good luck.
Ken
Norfolk, Va

 

[Gary Keramidas]

i assumed that the first sheet is where you want the list and row 1 has column
headers for columns A, B and C

Sub test()
Dim i As Long
For i = 2 To Worksheets.Count
With Worksheets(1)
.Range("A" & i) = Worksheets(i).Name
Worksheets(i).Range("m50:n50").Copy .Range("B" & i)
End With
Next
End Sub