Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create sorted list based on cell values | Excel Discussion (Misc queries) | |||
Macro to Create Worksheets Based on Cell Values | Excel Worksheet Functions | |||
Match values and create a list in one cell | Excel Discussion (Misc queries) | |||
Create a list of text from a single cell on several worksheets? | Excel Discussion (Misc queries) | |||
Count unique values and create list based on these values | Excel Worksheet Functions |