Loop through Excel sheets [Resolved]

[Pearso]

Ok all,
I have a question I'm not sure where to start with this. I would like to create a macro that looks at all the sheets in a work book and returns the value of cell C7 in each work book in a list (column A) on the first worksheet.

Can someone please point me in the right direction as to what i should be doing.

[RobDog888]

Welcome to the Forums.

Sure, but we need to clarify a few things first. A workbook has one or more sheets (or worksheets.
same object, different name). From your question I gather that you have several workbooks (Book1.xls, Book2.xls, etc.)
open that you need to get the C7 value from sheet1?

We can do this from VBA or VB6, which one would you prefer?

[Pearso]

I'm using VBA, What i would like to do is loop througt all the worksheets in a workbook and return the value in C7 on the first worksheet in a column.

[RobDog888]

Ok, so the results will be in sheet1 - A1.
So all the other sheets C7 are of what data type? Numeric?

[Pearso]

Yes all the C7 cells are Numeric (numbers).

Thanks for this help by the way.

[RobDog888]

Ok, this assumes that sheet 1 is going to be the sheet to receive the total and that the values totaled are numeric.

Public Sub TotalC7s2A1()
 
    Dim i As Integer
    Dim iTotal As Integer
    Dim oWB As Workbook
    
    iTotal = 0
    Set oWB = ActiveWorkbook
    For i = 2 To oWB.Sheets.Count
        iTotal = iTotal + oWB.Sheets(i).Cells(7, 3).Value
    Next
    oWB.Sheets(1).Cells(1, 1).Value = iTotal
    Set oWB = Nothing
    
End Sub

[Pearso]

Thank you very much that works a treat.

Is it possible to list the numbers down the page it A1 = C7 from worksheet1, A2 = C7 from worksheet2 and so on.

[RobDog888]

So no totaling but a column showing each sheets C7 value and sheet name?

[Pearso]

yeah thats it

I'm trying to create a basic report from a series of worksheets in a single workbook.

[RobDog888]

See if this is ok.

Public Sub TotalC7s2Sheet1()
 
    Dim i As Integer
    Dim x As Integer
    Dim sSheet As String
    Dim oWB As Workbook
    
    x = 1
    Set oWB = ActiveWorkbook
    For i = 2 To oWB.Sheets.Count
        oWB.Sheets(1).Cells(x, 1).Value = oWB.Sheets(i).Cells(7, 3).Value
        oWB.Sheets(1).Cells(x, 2).Value = "From: " & oWB.Sheets(i).Name
        x = x + 1
    Next
    Set oWB = Nothing
    
End Sub

[Pearso]

thankyou very much that works perfectly. I realy need to do some more reading on how to write VBA code.
One last question how do a mark this thread as resolved?

[RobDog888]

No prob. Glad to help.

You can Resolve your thread by editing your first post and either changing
the subject icon to the green check mark or adding "[Resolved]" to the end
of the subject text.

[Pearso]

Cheers Robdog