Multiple worksheets in Excel

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

  • I want to pull the same cells from every worksheet in a given workbook.
    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]
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion