Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get multiple cells from all worksheets Code Not Work
I just would like to get multiple cells from all the worksheets and have them
placed in one summary worksheet but this code stops at pdp = ("N50")???? Sub GetCellFromWrksheets() Dim wsNew As Worksheet, wsTemp As Worksheet Dim r As Range Dim pdp As Range Dim pmm As Range Set wsNew = Sheets.Add ' add a new worksheet wsNew.Name = "All_Sheets" ' named "All_Sheets" Set r = wsNew.Range("D1") ' 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 ActiveCell.Offset(0, 1) = pdp ActiveCell.Offset(0, 2) = pmm Next ws ' clean up Set ws = Nothing Set r = Nothing Set wsNew = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get multiple cells from all worksheets Code Not Work
Change your code to:
set pdp = Range("N50") set pmm = Range("M50") hth, Doug "Rookie_User" wrote in message ... I just would like to get multiple cells from all the worksheets and have them placed in one summary worksheet but this code stops at pdp = ("N50")???? Sub GetCellFromWrksheets() Dim wsNew As Worksheet, wsTemp As Worksheet Dim r As Range Dim pdp As Range Dim pmm As Range Set wsNew = Sheets.Add ' add a new worksheet wsNew.Name = "All_Sheets" ' named "All_Sheets" Set r = wsNew.Range("D1") ' 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 ActiveCell.Offset(0, 1) = pdp ActiveCell.Offset(0, 2) = pmm 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 | |||
Does SUMIF work over multiple worksheets? | Excel Discussion (Misc queries) | |||
Delete Blank Rows Code - Multiple Worksheets - Multiple Documents | Excel Discussion (Misc queries) | |||
How do I print multiple worksheets of a work book onto one page? | Excel Discussion (Misc queries) | |||
change code to work over multiple sheets | Excel Programming | |||
Add VBA code to multiple WorkSheets | Excel Programming |