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