Get multiple cells from all worksheets Code Not Work

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Back
Top