VBA Excel how to activate macro for sheet 2, 3 and 4 from sheet 1

Question

I have 3 workbooks in one folder.
I use macro to copy each Sheet1 in every workbook in that folder into my workbook example.
In my workbook example now I have 4 sheets named sheet1, sheet1 (4), sheet1 (3), sheet1 (2).

I want to use a button form so when I click it, the code (below) run for any other sheets except sheet one.

Sub Copy_Sum()
    Dim ws As Worksheet
    'Selecting the worksheets to loop through
    K = 1
    For Each ws In ThisWorkbook.Worksheets
    'Skiping the sheet1
        If ws.Name <> "Sheet1" Then
    'Counting the number of rows for automation
            rowscount = Cells(Rows.Count, 1).End(xlUp).Row
            temp = 0
    'add name
            Cells(rowscount + 1, 8) = "Jumlah"
            Cells(rowscount + 2, 8) = "Mutasi"
    'Looping throught the cells for the calculation
            For j = 2 To (rowscount)
               'Counting the number of cells which value greater than zero
                If Cells(j, 9) > 0 Then
                    temp = temp + 1
                End If
            Next j
    'Counting the number of rows for automation
            rowscount1 = Cells(Rows.Count, 1).End(xlUp).Row
            temp1 = 0
            For i = 2 To (rowscount1)
            'Counting the number of cells which value greater than zero
                If Cells(i, 10) > 0 Then
                    temp1 = temp1 + 1
                End If
            Next i
     'Summing up the values which are above the current cell
     'and in Sheet1, this inclues negative numbers as well
            Cells(rowscount + 1, 9).Value = Application.Sum(Range(Cells(1, 9), _
                Cells(rowscount, 9)))
            Cells(rowscount + 2, 9) = temp
            Cells(rowscount1 + 1, 10).Value = Application.Sum(Range(Cells(1, 10), _
                Cells(rowscount1, 10)))
            Cells(rowscount1 + 2, 10) = temp1
        End If
    Next ws
End Sub

I'm don't fully understand the macro code.
This code was made by editing the code from NEOmen and I really appreciate it.
This is code supposed to automatically loop the code for each sheet except sheet1 but it didn't work.
I must run the code manually in sheet1 (4), sheet1 (3), sheet1 (2) to get it done.
I think I can edit it a little bit like what I wanted, but I can't.
I got stuck in the end.

the code after revision from @chris neilsen @L42

Sub Copy_Sum()

Dim ws As Worksheet
'Selecting the worksheets to loop through
K = 1
For Each ws In ThisWorkbook.Worksheets
'Skiping the sheet1
With ws
    If .Name <> "Sheet1" Then
'Counting the number of rows for automation
         rowscount = .Cells(.Rows.Count, 1).End(xlUp).Row
         temp = 0
'add name
        .Cells(rowscount + 1, 8) = "Jumlah"
        .Cells(rowscount + 2, 8) = "Mutasi"
'Looping throught the cells for the calculation
             For j = 2 To (rowscount)
           'Counting the number of cells which value greater than zero
                  If .Cells(j, 9) > 0 Then
                  temp = temp + 1
                  End If
              Next j

'Counting the number of rows for automation
         rowscount1 = .Cells(.Rows.Count, 1).End(xlUp).Row
         temp1 = 0

              For i = 2 To (rowscount1)
           'Counting the number of cells which value greater than zero
                  If .Cells(i, 10) > 0 Then
                  temp1 = temp1 + 1
                  End If
              Next i

 'Summing up the values which are above the current cell and in Sheet1, this inclues negative numbers as well

             .Cells(rowscount + 1, 9).Value = Application.Sum(.Range(.Cells(1, 9), .Cells(rowscount, 9)))
             .Cells(rowscount + 2, 9) = temp

             .Cells(rowscount1 + 1, 10).Value = Application.Sum(.Range(.Cells(1, 10), .Cells(rowscount1, 10)))
             .Cells(rowscount1 + 2, 10) = temp1
             'copy ke sheet 1

             End If
    End With
Next ws

End Sub

All the Cells and Range references refer to the ActiveSheet. Perhaps they should be ws.Cells and ws.Range — chris neilsen
@chrisneilsen ah didn't see your comment while posting my answer. If you wish to add it as answer, I'll delete mine :) — L42
woooo.. thanks a lot @chrisneilsen it really solved my problem. i tried to figure it out for about a week now but it finally finished with your help thanks again :D if you don't mind i would like to ask. why i need to add dot to every cells and range to make it right? i can't understand even now when the code become right. ah and sorry for my bad english. — Aditya Nugroho
woooo.. thanks a lot @L42 it really solved my problem. i tried to figure it out for about a week now but it finally finished with your help thanks again :D if you don't mind i would like to ask. why i need to add dot to every cells and range to make it right? i can't understand even now when the code become right. ah and sorry for my bad english. since only one user can be mentioned once a time so i write another comment. — Aditya Nugroho

L42 L42 · Accepted Answer · 2015-01-09T06:17:29

The problem is you're not referencing the object correctly.
Try fully qualifying your objects by using With Statement.

For Each ws In Thisworkbook.Worksheets
    With ws 'add With statement to explicitly reference ws object
        'precede all properties with a dot from here on
        If .Name <> "Sheet1" Then
            rowscount = .Cells(.Rows.Count, 1).End(xlUp).Row 'notice the dots
            temp = 0
            '~~> do the same with the rest of the code

        End If
    End With
Next

VBA Excel how to activate macro for sheet 2, 3 and 4 from sheet 1

1 Answers