Hey guys, (sorry for the cross post, but the last post was in the wrong forum)
I'm looking for what my naive self thinks should be an easy script. I want to pull the same cells from every worksheet in a given workbook.
Let's say the cells are A20, B25, C2, D10. Really there are about 70 cells on each worksheet that I need, but they always stay the same.
Any ideas?
Comments
Let's say the cells are A20, B25, C2, D10
Hi idledebonair,
I guess this is what you asked for
[code]Sub CellsFromSheets()
'-----------------------------------------------
'First create a new Worksheet
'Then copy the cell values A20, B25, C2, D10 etc
'I'm also copying the names of the Worksheets
'as well as the addresses of the copied cells
'so that you can cross-check the results
'-----------------------------------------------
Dim S, Sht As Worksheet
Dim cell As Range
Dim i, titleRow, newRow, newCol As Integer
Set Sht = Worksheets.Add
newRow = 1
titleRow = 1
For Each S In ThisWorkbook.Worksheets
If S.Name <> Sht.Name Then
newRow = newRow + 1
newCol = 1
'The name of the Worksheet being copied
Sht.Cells(newRow, newCol).Value = S.Name
newCol = newCol + 1
'The address of the cell being copied
Sht.Cells(titleRow, newCol).Value = _
Replace(S.Range("A2").Address, "$", "")
'The value of the cell being copied
Sht.Cells(newRow, newCol).Value = _
S.Range("A2").Value
newCol = newCol + 1
Sht.Cells(titleRow, newCol).Value = _
Replace(S.Range("B25").Address, "$", "")
Sht.Cells(newRow, newCol).Value = _
S.Range("B25").Value
newCol = newCol + 1
Sht.Cells(titleRow, newCol).Value = _
Replace(S.Range("C2").Address, "$", "")
Sht.Cells(newRow, newCol).Value = _
S.Range("C2").Value
'...and so on
End If
Next
ActiveSheet.Cells.EntireColumn.AutoFit
End Sub[/code]