Create list of worksheets and cell values
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 |
Create list of worksheets and cell values
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 |
Create list of worksheets and cell values
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" wrote in message ... 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 |
All times are GMT +1. The time now is 03:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com