virtualboy2013
New Member
- Joined
- Jul 7, 2014
- Messages
- 6
Hi, I am just a beginner in VBA. Appreciate your help in modifying the code below:
Public Sub CommandButton2_Click()
Call Sheet1.CommandButton1_Click
Sheet8.Activate
'101-WP-101
Sheets("Data Normalize").Range("T19:T30").Copy Destination:=Sheets("summary").Range("B12")
Sheets("Data Normalize").Range("E19:E30").Copy Destination:=Sheets("summary").Range("C12")
'101-WP-102
Sheets("Data Normalize").Range("U19:U30").Copy Destination:=Sheets("summary").Range("E12")
Sheets("Data Normalize").Range("F19:F30").Copy Destination:=Sheets("summary").Range("F12")
'101-WP-103
Sheets("Data Normalize").Range("V19:V30").Copy Destination:=Sheets("summary").Range("H12")
Sheets("Data Normalize").Range("G19:G30").Copy Destination:=Sheets("summary").Range("I12")
'101-WP-104
Sheets("Data Normalize").Range("W19:W30").Copy Destination:=Sheets("summary").Range("B27")
Sheets("Data Normalize").Range("H19:H30").Copy Destination:=Sheets("summary").Range("C27")
'101-WP-105
Sheets("Data Normalize").Range("X19:X30").Copy Destination:=Sheets("summary").Range("E27")
Sheets("Data Normalize").Range("I19:I30").Copy Destination:=Sheets("summary").Range("F27")
'102-WP-101
Sheets("Data Normalize").Range("Y19:Y30").Copy Destination:=Sheets("summary").Range("B43")
Sheets("Data Normalize").Range("J19:J30").Copy Destination:=Sheets("summary").Range("C43")
'102-WP-102
Sheets("Data Normalize").Range("Z19:Z30").Copy Destination:=Sheets("summary").Range("E43")
Sheets("Data Normalize").Range("K19:K30").Copy Destination:=Sheets("summary").Range("F43")
'102-WP-103
Sheets("Data Normalize").Range("AA19:AA30").Copy Destination:=Sheets("summary").Range("H43")
Sheets("Data Normalize").Range("L19:L30").Copy Destination:=Sheets("summary").Range("I43")
'102-WP-104
Sheets("Data Normalize").Range("AB19:AB30").Copy Destination:=Sheets("summary").Range("B58")
Sheets("Data Normalize").Range("M19:M30").Copy Destination:=Sheets("summary").Range("C58")
'102-WP-105
Sheets("Data Normalize").Range("AC19:AC30").Copy Destination:=Sheets("summary").Range("E58")
Sheets("Data Normalize").Range("N19:N30").Copy Destination:=Sheets("summary").Range("F58")
'102-WP-106
Sheets("Data Normalize").Range("AD19:AD30").Copy Destination:=Sheets("summary").Range("H58")
Sheets("Data Normalize").Range("O19:O30").Copy Destination:=Sheets("summary").Range("I58")
'102-WP-107
Sheets("Data Normalize").Range("AE19:AE30").Copy Destination:=Sheets("summary").Range("B73")
Sheets("Data Normalize").Range("P19:P30").Copy Destination:=Sheets("summary").Range("C73")
'102-WP-108
Sheets("Data Normalize").Range("AF19:AF30").Copy Destination:=Sheets("summary").Range("E73")
Sheets("Data Normalize").Range("Q19:Q30").Copy Destination:=Sheets("summary").Range("F73")
'102-WP-109
Sheets("Data Normalize").Range("AG19:AG30").Copy Destination:=Sheets("summary").Range("H73")
Sheets("Data Normalize").Range("R19:R30").Copy Destination:=Sheets("summary").Range("I73")
'102-WP-110
Sheets("Data Normalize").Range("AH19:AH30").Copy Destination:=Sheets("summary").Range("B88")
Sheets("Data Normalize").Range("S19:S30").Copy Destination:=Sheets("summary").Range("C88")
Call SumDuplicates(12, 2)
Call SumDuplicates(12, 5)
Call SumDuplicates(12, 8)
Call SumDuplicates(27, 2)
Call SumDuplicates(27, 5)
Call SumDuplicates(43, 2)
Call SumDuplicates(43, 5)
Call SumDuplicates(43, 8)
Call SumDuplicates(58, 2)
Call SumDuplicates(58, 5)
Call SumDuplicates(58, 8)
Call SumDuplicates(73, 2)
Call SumDuplicates(73, 5)
Call SumDuplicates(73, 8)
Call SumDuplicates(88, 2)
End Sub
Private Function SumDuplicates(startRow As Integer, startCol As Integer)
Dim lngRow As Long
With ActiveSheet
lngRow = .Cells(startRow, startCol).End(xlDown).Row
.Cells(startRow, startCol).CurrentRegion.sort key1:=.Cells(startRow, startCol), Header:=xlYes 'change this to xlYes if your table has header cells
.Cells(1, 1).Value = lngRow
Do
If .Cells(lngRow - 1, startCol) = .Cells(lngRow, startCol) Then
.Cells(lngRow - 1, startCol + 1) = .Cells(lngRow - 1, startCol + 1) + .Cells(lngRow, startCol + 1)
.Cells(lngRow, startCol).Clear
.Cells(lngRow, startCol + 1).Clear
End If
lngRow = lngRow - 1
Loop Until lngRow < (startRow + 1)
End With
End Function
When I run the code, it is stopping on the text highlighted/underline in red.
I have multiple tables in my worksheet.
Appreciate any help.
Public Sub CommandButton2_Click()
Call Sheet1.CommandButton1_Click
Sheet8.Activate
'101-WP-101
Sheets("Data Normalize").Range("T19:T30").Copy Destination:=Sheets("summary").Range("B12")
Sheets("Data Normalize").Range("E19:E30").Copy Destination:=Sheets("summary").Range("C12")
'101-WP-102
Sheets("Data Normalize").Range("U19:U30").Copy Destination:=Sheets("summary").Range("E12")
Sheets("Data Normalize").Range("F19:F30").Copy Destination:=Sheets("summary").Range("F12")
'101-WP-103
Sheets("Data Normalize").Range("V19:V30").Copy Destination:=Sheets("summary").Range("H12")
Sheets("Data Normalize").Range("G19:G30").Copy Destination:=Sheets("summary").Range("I12")
'101-WP-104
Sheets("Data Normalize").Range("W19:W30").Copy Destination:=Sheets("summary").Range("B27")
Sheets("Data Normalize").Range("H19:H30").Copy Destination:=Sheets("summary").Range("C27")
'101-WP-105
Sheets("Data Normalize").Range("X19:X30").Copy Destination:=Sheets("summary").Range("E27")
Sheets("Data Normalize").Range("I19:I30").Copy Destination:=Sheets("summary").Range("F27")
'102-WP-101
Sheets("Data Normalize").Range("Y19:Y30").Copy Destination:=Sheets("summary").Range("B43")
Sheets("Data Normalize").Range("J19:J30").Copy Destination:=Sheets("summary").Range("C43")
'102-WP-102
Sheets("Data Normalize").Range("Z19:Z30").Copy Destination:=Sheets("summary").Range("E43")
Sheets("Data Normalize").Range("K19:K30").Copy Destination:=Sheets("summary").Range("F43")
'102-WP-103
Sheets("Data Normalize").Range("AA19:AA30").Copy Destination:=Sheets("summary").Range("H43")
Sheets("Data Normalize").Range("L19:L30").Copy Destination:=Sheets("summary").Range("I43")
'102-WP-104
Sheets("Data Normalize").Range("AB19:AB30").Copy Destination:=Sheets("summary").Range("B58")
Sheets("Data Normalize").Range("M19:M30").Copy Destination:=Sheets("summary").Range("C58")
'102-WP-105
Sheets("Data Normalize").Range("AC19:AC30").Copy Destination:=Sheets("summary").Range("E58")
Sheets("Data Normalize").Range("N19:N30").Copy Destination:=Sheets("summary").Range("F58")
'102-WP-106
Sheets("Data Normalize").Range("AD19:AD30").Copy Destination:=Sheets("summary").Range("H58")
Sheets("Data Normalize").Range("O19:O30").Copy Destination:=Sheets("summary").Range("I58")
'102-WP-107
Sheets("Data Normalize").Range("AE19:AE30").Copy Destination:=Sheets("summary").Range("B73")
Sheets("Data Normalize").Range("P19:P30").Copy Destination:=Sheets("summary").Range("C73")
'102-WP-108
Sheets("Data Normalize").Range("AF19:AF30").Copy Destination:=Sheets("summary").Range("E73")
Sheets("Data Normalize").Range("Q19:Q30").Copy Destination:=Sheets("summary").Range("F73")
'102-WP-109
Sheets("Data Normalize").Range("AG19:AG30").Copy Destination:=Sheets("summary").Range("H73")
Sheets("Data Normalize").Range("R19:R30").Copy Destination:=Sheets("summary").Range("I73")
'102-WP-110
Sheets("Data Normalize").Range("AH19:AH30").Copy Destination:=Sheets("summary").Range("B88")
Sheets("Data Normalize").Range("S19:S30").Copy Destination:=Sheets("summary").Range("C88")
Call SumDuplicates(12, 2)
Call SumDuplicates(12, 5)
Call SumDuplicates(12, 8)
Call SumDuplicates(27, 2)
Call SumDuplicates(27, 5)
Call SumDuplicates(43, 2)
Call SumDuplicates(43, 5)
Call SumDuplicates(43, 8)
Call SumDuplicates(58, 2)
Call SumDuplicates(58, 5)
Call SumDuplicates(58, 8)
Call SumDuplicates(73, 2)
Call SumDuplicates(73, 5)
Call SumDuplicates(73, 8)
Call SumDuplicates(88, 2)
End Sub
Private Function SumDuplicates(startRow As Integer, startCol As Integer)
Dim lngRow As Long
With ActiveSheet
lngRow = .Cells(startRow, startCol).End(xlDown).Row
.Cells(startRow, startCol).CurrentRegion.sort key1:=.Cells(startRow, startCol), Header:=xlYes 'change this to xlYes if your table has header cells
.Cells(1, 1).Value = lngRow
Do
If .Cells(lngRow - 1, startCol) = .Cells(lngRow, startCol) Then
.Cells(lngRow - 1, startCol + 1) = .Cells(lngRow - 1, startCol + 1) + .Cells(lngRow, startCol + 1)
.Cells(lngRow, startCol).Clear
.Cells(lngRow, startCol + 1).Clear
End If
lngRow = lngRow - 1
Loop Until lngRow < (startRow + 1)
End With
End Function
When I run the code, it is stopping on the text highlighted/underline in red.
I have multiple tables in my worksheet.
Appreciate any help.