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

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


Rookie_User wrote:
> 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

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


--


Gary


"Rookie_User" <(E-Mail Removed)> wrote in message
news:CED53705-5B11-4E26-95A0-(E-Mail Removed)...
>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