How do I adjust code so that I can run VBA with out being on Sheet1?

NHagedorn

New Member
Joined
May 11, 2012
Messages
35
Sub works great, but I must be on sheet1 before it will add numbers to sheet2. How do I adjust code so that I can run vba no matter what sheet I'm on. This is a hassle because I have 15 of these and switching back and forth is time consuming.



Sub actualAPR()
Dim a As Long, b As Long, c As Long, d As Long, f As Long, g As Long, h As Long, j As Long, k As Long, m As Long
Dim lastrow As Long
Dim I As Integer

a = 0
b = 0
c = 0
d = 0
f = 0
g = 0
h = 0
j = 0
k = 0
m = 0

lastrow = Sheet1.Cells(Rows.Count, 22).End(xlUp).Row
For I = 2 To lastrow
If Cells(I, 2) = "83A00" Then
a = Cells(I, 22) + a
ElseIf Cells(I, 2) = "83B00" Then
b = Cells(I, 22) + b
ElseIf Cells(I, 2) = "83C00" Then
c = Cells(I, 22) + c
ElseIf Cells(I, 2) = "83D00" Then
d = Cells(I, 22) + d
ElseIf Cells(I, 2) = "83F00" Then
f = Cells(I, 22) + f
ElseIf Cells(I, 2) = "83G00" Then
g = Cells(I, 22) + g
ElseIf Cells(I, 2) = "83H00" Then
h = Cells(I, 22) + h
ElseIf Cells(I, 2) = "83J00" Then
j = Cells(I, 22) + j
ElseIf Cells(I, 2) = "83K00" Then
k = Cells(I, 22) + k
ElseIf Cells(I, 2) = "83M00" Then
m = Cells(I, 22) + m

End If
Next I
Sheet9.Select

Range("A3") = "83A00"
Range("N3") = a
Range("A4") = "83B00"
Range("N4") = b
Range("A5") = "83C00"
Range("N5") = c
Range("A6") = "83D00"
Range("N6") = d
Range("A7") = "83F00"
Range("N7") = f
Range("A8") = "83G00"
Range("N8") = g
Range("A9") = "83H00"
Range("N9") = h
Range("A10") = "83J00"
Range("N10") = j
Range("A11") = "83K00"
Range("N11") = k
Range("A12") = "83M00"
Range("N12") = m

End Sub
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,174
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
Sub actualAPR()
   Dim a As Long, b As Long, c As Long, d As Long, f As Long, g As Long, h As Long, j As Long, k As Long, m As Long
   Dim lastrow As Long
   Dim I As Integer
   
   a = 0
   b = 0
   c = 0
   d = 0
   f = 0
   g = 0
   h = 0
   j = 0
   k = 0
   m = 0
   With Sheet1
      lastrow = .Cells(Rows.Count, 22).End(xlUp).Row
      For I = 2 To lastrow
         If .Cells(I, 2) = "83A00" Then
            a = .Cells(I, 22) + a
         ElseIf .Cells(I, 2) = "83B00" Then
            b = .Cells(I, 22) + b
         ElseIf .Cells(I, 2) = "83C00" Then
            c = .Cells(I, 22) + c
         ElseIf .Cells(I, 2) = "83D00" Then
            d = .Cells(I, 22) + d
         ElseIf .Cells(I, 2) = "83F00" Then
            f = .Cells(I, 22) + f
         ElseIf .Cells(I, 2) = "83G00" Then
            g = .Cells(I, 22) + g
         ElseIf .Cells(I, 2) = "83H00" Then
            h = .Cells(I, 22) + h
         ElseIf .Cells(I, 2) = "83J00" Then
            j = .Cells(I, 22) + j
         ElseIf .Cells(I, 2) = "83K00" Then
            k = .Cells(I, 22) + k
         ElseIf .Cells(I, 2) = "83M00" Then
            m = .Cells(I, 22) + m
         
         End If
      Next I
   End With
   With Sheet9
      .Range("A3") = "83A00"
      .Range("N3") = a
      .Range("A4") = "83B00"
      .Range("N4") = b
      .Range("A5") = "83C00"
      .Range("N5") = c
      .Range("A6") = "83D00"
      .Range("N6") = d
      .Range("A7") = "83F00"
      .Range("N7") = f
      .Range("A8") = "83G00"
      .Range("N8") = g
      .Range("A9") = "83H00"
      .Range("N9") = h
      .Range("A10") = "83J00"
      .Range("N10") = j
      .Range("A11") = "83K00"
      .Range("N11") = k
      .Range("A12") = "83M00"
      .Range("N12") = m
   End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,052
Messages
5,599,511
Members
414,315
Latest member
Yolanda5050

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top