Single Command Button to run multiple Worksheets

[Lawrence]

Forgive my trivial question, but I am a total newbie when it comes to VBA. I am just starting and I came up with this code after lots of reading and trial and error, although probably not the most efficient one.

I have this code pasted in Worksheet1 and Worksheet2 with a CommandButton1 on each one to fire the code. What I am trying to do is to have a single CommandButton1 that would fire the code though all the specified sheets, instead of having to go to each sheet and hit the button each time.

Lastly, is there a way to dynamically populate? Right now, I am telling to put the value for lngSelect1 and lngSelect2 in a specific cell (H17 and H18). The problem is that I have about 500 values to return and if there are any changes, I have to manually slide everything. Could I set the first values (for lngSelect1) and have VBA return all the others one cell/row down?

Thanks for your help
[vba]Option Explicit
Dim glngDataTotalRows As Long

Private Sub CommandButton2_Click()
Application.EnableEvents = False
Sheets(Array("Sheet1", "Sheet2")).PrintPreview
Application.EnableEvents = True
End Sub

Private Sub CommandButton1_Click()
glngDataTotalRows = lngLastRow
UpdateDataTable
MsgBox "The report is ready"
End Sub

Private Function lngLastRow() As Long
Dim lngDataTotalRows As Long
lngDataTotalRows = 1
Do Until Worksheets(strDataTab).Cells(lngDataTotalRows, 1).Value = ""
lngDataTotalRows = lngDataTotalRows + 1
Loop
lngLastRow = lngDataTotalRows - 1
End Function

Private Function blnIsRegion(lngRowIndex As Long)
If Worksheets(strDataTab).Cells(lngRowIndex, 2).Value = Worksheets(Me.Name).Range("H15").Value Then
blnIsRegion = True
Else
blnIsRegion = False
End If
End Function

Private Function blnIsDivision(lngRowIndex As Long)
If Worksheets(strDataTab).Cells(lngRowIndex, 3).Value = Worksheets(Me.Name).Range("H15").Value Then
blnIsDivision = True
Else
blnIsDivision = False
End If
End Function

Private Function blnIsState(lngRowIndex As Long)
If Worksheets(strDataTab).Cells(lngRowIndex, 37).Value = Worksheets(Me.Name).Range("H15").Value Then
blnIsState = True
Else
blnIsState = False
End If
End Function

Private Sub Worksheet_Activate()

End Sub

Private Function strDataTab() As String
strDataTab = Me.Name & "-Data"
End Function

Private Sub UpdateDataTable()
Dim lngRowIndex As Long
Dim lngSelect1 As Long,
Dim lngSelect2 As Long,

For lngRowIndex = 2 To glngDataTotalRows
If blnIsRegion(lngRowIndex) = True Or blnIsDivision(lngRowIndex) = True Or blnIsState(lngRowIndex) = True Then

Select Case Worksheets(strDataTab).Cells(lngRowIndex, 3).Value
Case "ABC"
lngSelect1 = lngSelect1 + 1
Case "DEF"
lngSelect2 = lngSelect2 + 1
End Select

End If
Next
'Populate
Worksheets(Me.Name).Range("H17").Value = lngSelect1
Worksheets(Me.Name).Range("H18").Value = lngSelect2
[/vba]

[Bob Phillips]

[vba]


Private Sub CommandButton2_Click()
Application.EnableEvents = False
Sheets(Array("CuWorksheet1", "Worksheet2")).PrintPreview
Application.EnableEvents = True
End Sub

Private Sub CommandButton1_Click()
Dim itm As Variant

For Each itm In Array("Sheet1", "Sheet2")
UpdateDataTable itm
Next itm
MsgBox "The report is ready"
End Sub

Private Sub UpdateDataTable(ByVal strDataTab As String)
Dim glngDataTotalRows As Long
Dim lngRowIndex As Long
Dim lngSelect1 As Long
Dim lngSelect2 As Long

For lngRowIndex = 2 To lngLastRow(strDataTab)
If blnIsRegion(strDataTab, lngRowIndex) = True Or _
blnIsDivision(strDataTab, lngRowIndex) = True Or _
blnIsState(strDataTab, lngRowIndex) = True Then

Select Case Worksheets(strDataTab & "-Data").Cells(lngRowIndex, 3).Value
Case "ABC"
lngSelect1 = lngSelect1 + 1
Case "DEF"
lngSelect2 = lngSelect2 + 1
End Select
End If
Next
'Populate
Worksheets(strDataTab).Range("H17").Value = lngSelect1
Worksheets(strDataTab).Range("H18").Value = lngSelect2
End Sub

Private Function lngLastRow(ByVal strDataTab As String) As Long
Dim lngDataTotalRows As Long
lngDataTotalRows = 1
Do Until Worksheets(strDataTab).Cells(lngDataTotalRows, 1).Value = ""
lngDataTotalRows = lngDataTotalRows + 1
Loop
lngLastRow = lngDataTotalRows - 1
End Function

Private Function blnIsRegion(ByVal strDataTab As String, lngRowIndex As Long)
blnIsRegion = Worksheets(strDataTab).Cells(lngRowIndex, 2).Value = Worksheets(strDataTab).Range("H15").Value
End Function

Private Function blnIsDivision(ByVal strDataTab As String, lngRowIndex As Long)
blnIsDivision = Worksheets(strDataTab).Cells(lngRowIndex, 3).Value = Worksheets(strDataTab).Range("H15").Value
End Function

Private Function blnIsState(ByVal strDataTab As String, lngRowIndex As Long)
blnIsState = Worksheets(strDataTab).Cells(lngRowIndex, 37).Value = Worksheets(strDataTab).Range("H15").Value
End Function
[/vba]

[Lawrence]

Thank you, it helped run the code though the worksheets, but now it is returning zeros in every cell ???

[mdmackillop]

Can you post some sample data?

[Lawrence]

Attached is a simplified sample. You make your selection in cell J5 and then click the red button to update the data. I put the same code in each sheet because the selection you make on the "Current Day" sheet in cell J5 is carried through all other sheets. I can't figure out how to have a single CommandButton1 that would update both "Current Day" and "Prior Day" output sheets instead of having to do it for every output sheet. The ultimate would be to have the data automatically update based on cell J5 selection, but I need to get better first

Edit: This is with the original code I posted, not with XLD's edits

[Lawrence]

Anyone? As mentioned earlier, xld's code posted above goes through both sheets, however it zero's everything out.

[Bob Phillips]

This is quite a bit different to what you shoewed us earlier.

[vba]

Option Explicit

Private Sub CommandButton2_Click()
Application.EnableEvents = False
Sheets(Array("Current Day", "Prior Day")).PrintPreview
Application.EnableEvents = True
End Sub

Private Sub CommandButton1_Click()
Dim itm As Variant

For Each itm In Array("Current Day", "Prior Day")
UpdateDataTable itm
Next itm
MsgBox "The report is ready"
End Sub

Private Sub UpdateDataTable(ByVal strDataTab As String)
Dim glngDataTotalRows As Long
Dim lngRowIndex As Long
Dim lngSelect1 As Long, lngOthers1 As Long
Dim lngSelect2 As Long, lngOthers2 As Long
Dim lngSelect3 As Long, lngOthers3 As Long
Dim lngSelect4 As Long, lngOthers4 As Long
Dim lngSelect5 As Long, lngOthers5 As Long
Dim lngSelect6 As Long, lngOthers6 As Long
Dim lngSelect7 As Long, lngOthers7 As Long
Dim lngSelect8 As Long, lngOthers8 As Long

For lngRowIndex = 6 To lngLastRow(strDataTab)
If blnIsRegion(strDataTab, lngRowIndex) = True Then

Select Case Worksheets(strDataTab).Cells(lngRowIndex, "C").Value
Case "ABC": lngSelect1 = lngSelect1 + 1
Case "DEF": lngSelect2 = lngSelect2 + 1
Case "XYZ": lngSelect3 = lngSelect3 + 1
Case "East": lngSelect4 = lngSelect4 + 1
Case "West": lngSelect5 = lngSelect5 + 1
Case "South": lngSelect6 = lngSelect6 + 1
Case "AK": lngSelect7 = lngSelect7 + 1
Case "CA": lngSelect8 = lngSelect8 + 1
End Select

Else
Select Case Worksheets(strDataTab).Cells(lngRowIndex, 1).Value
Case "ABC": lngOthers1 = lngOthers1 + 1
Case "DEF": lngOthers2 = lngOthers2 + 1
Case "XYZ": lngOthers3 = lngOthers3 + 1
Case "East": lngOthers4 = lngOthers4 + 1
Case "West": lngOthers5 = lngOthers5 + 1
Case "South": lngOthers6 = lngOthers6 + 1
Case "AK": lngOthers7 = lngOthers7 + 1
Case "CA": lngOthers8 = lngOthers8 + 1
End Select
End If
Next

Worksheets(strDataTab).Range("E6").Value = lngSelect1
Worksheets(strDataTab).Range("E7").Value = lngSelect2
Worksheets(strDataTab).Range("E8").Value = lngSelect3
Worksheets(strDataTab).Range("E9").Value = lngSelect4
Worksheets(strDataTab).Range("E10").Value = lngSelect5
Worksheets(strDataTab).Range("E11").Value = lngSelect6
Worksheets(strDataTab).Range("E12").Value = lngSelect7
Worksheets(strDataTab).Range("E13").Value = lngSelect8

Worksheets(strDataTab).Range("F6").Value = lngOthers1
Worksheets(strDataTab).Range("F7").Value = lngOthers2
Worksheets(strDataTab).Range("F8").Value = lngOthers3
Worksheets(strDataTab).Range("F9").Value = lngOthers4
Worksheets(strDataTab).Range("F10").Value = lngOthers5
Worksheets(strDataTab).Range("F11").Value = lngOthers6
Worksheets(strDataTab).Range("F12").Value = lngOthers7
Worksheets(strDataTab).Range("F13").Value = lngOthers8
End Sub

Private Function lngLastRow(ByVal strDataTab As String) As Long
Dim lngDataTotalRows As Long
lngDataTotalRows = 6
Do Until Worksheets(strDataTab).Cells(lngDataTotalRows, "C").Value = ""
lngDataTotalRows = lngDataTotalRows + 1
Loop
lngLastRow = lngDataTotalRows - 1
End Function

Private Function blnIsRegion(ByVal strDataTab As String, lngRowIndex As Long)
blnIsRegion = Worksheets(strDataTab).Cells(lngRowIndex, 2).Value = Worksheets(strDataTab).Range("H15").Value
End Function

Private Function blnIsDivision(ByVal strDataTab As String, lngRowIndex As Long)
blnIsDivision = Worksheets(strDataTab).Cells(lngRowIndex, 3).Value = Worksheets(strDataTab).Range("H15").Value
End Function

Private Function blnIsState(ByVal strDataTab As String, lngRowIndex As Long)
blnIsState = Worksheets(strDataTab).Cells(lngRowIndex, 37).Value = Worksheets(strDataTab).Range("H15").Value
End Function
[/vba]

[Lawrence]

This is quite a bit different to what you shoewed us earlier.

Sorry for not being clear enough.

Basically, in the sample file I attached, the output sheets are "Current Day" and "Prior Day", and the input data sheets (lots of raw data will be there) are "Current Day-Data" and "Prior Day-Data". The idea is for the code to look at the corresponding input data sheet and to return the count for each item on the appropriate output sheet. It works but I have to hit the CommandButton1 on each sheet to fire each code,

Did I explain it better?

[Bob Phillips]

I would have thought it is all best done by formulae, but what is this test trying to check

If blnIsRegion(strDataTab, lngRowIndex) Then

[Lawrence]

I would have thought it is all best done by formulae, but what is this test trying to check

If blnIsRegion(strDataTab, lngRowIndex) Then

Sorry about the confusion, I just started this a couple weeks ago and this is my first code.

In the input sheets, I have roughly 150 columns and 40,000 rows. The statement above checks for a certain attribute in a specified column. If there is a match, it is true and then it returns the count in a certain column. If not a match, it is false, and then returns the count in another column.

In my original post, I had 3 of these statements because my drop down menu takes values from 3 different columns. However, in the simplified example attached, it would look at Column 1 in the corresponding Data tab and if it is a matches the selection in cell E5, it returns the count in column E. If not a match, it returns the count in column F instead.

The following code you gave me works, however it looks at the output sheet instead of looking at the input (corresponding data) sheet, so it returns 1 in every cell because it finds only one instance of everything.

[vba]
Option Explicit

Private Sub CommandButton2_Click()
Application.EnableEvents = False
Sheets(Array("Current Day", "Prior Day")).PrintPreview
Application.EnableEvents = True
End Sub

Private Sub CommandButton1_Click()
Dim itm As Variant

For Each itm In Array("Current Day", "Prior Day")
UpdateDataTable itm
Next itm
MsgBox "The report is ready"
End Sub

Private Sub UpdateDataTable(ByVal strDataTab As String)
Dim glngDataTotalRows As Long
Dim lngRowIndex As Long
Dim lngSelect1 As Long, lngOthers1 As Long
Dim lngSelect2 As Long, lngOthers2 As Long
Dim lngSelect3 As Long, lngOthers3 As Long
Dim lngSelect4 As Long, lngOthers4 As Long
Dim lngSelect5 As Long, lngOthers5 As Long
Dim lngSelect6 As Long, lngOthers6 As Long
Dim lngSelect7 As Long, lngOthers7 As Long
Dim lngSelect8 As Long, lngOthers8 As Long

For lngRowIndex = 6 To lngLastRow(strDataTab)
If blnIsRegion(strDataTab, lngRowIndex) = True Then

Select Case Worksheets(strDataTab).Cells(lngRowIndex, 3).Value
Case "ABC": lngSelect1 = lngSelect1 + 1
Case "DEF": lngSelect2 = lngSelect2 + 1
Case "XYZ": lngSelect3 = lngSelect3 + 1
Case "East": lngSelect4 = lngSelect4 + 1
Case "West": lngSelect5 = lngSelect5 + 1
Case "South": lngSelect6 = lngSelect6 + 1
Case "AK": lngSelect7 = lngSelect7 + 1
Case "CA": lngSelect8 = lngSelect8 + 1

End Select

Else
Select Case Worksheets(strDataTab).Cells(lngRowIndex, 3).Value
Case "ABC": lngOthers1 = lngOthers1 + 1
Case "DEF": lngOthers2 = lngOthers2 + 1
Case "XYZ": lngOthers3 = lngOthers3 + 1
Case "East": lngOthers4 = lngOthers4 + 1
Case "West": lngOthers5 = lngOthers5 + 1
Case "South": lngOthers6 = lngOthers6 + 1
Case "AK": lngOthers7 = lngOthers7 + 1
Case "CA": lngOthers8 = lngOthers8 + 1
End Select
End If
Next

Worksheets(strDataTab).Range("E6").Value = lngSelect1
Worksheets(strDataTab).Range("E7").Value = lngSelect2
Worksheets(strDataTab).Range("E8").Value = lngSelect3
Worksheets(strDataTab).Range("E9").Value = lngSelect4
Worksheets(strDataTab).Range("E10").Value = lngSelect5
Worksheets(strDataTab).Range("E11").Value = lngSelect6
Worksheets(strDataTab).Range("E12").Value = lngSelect7
Worksheets(strDataTab).Range("E13").Value = lngSelect8

Worksheets(strDataTab).Range("F6").Value = lngOthers1
Worksheets(strDataTab).Range("F7").Value = lngOthers2
Worksheets(strDataTab).Range("F8").Value = lngOthers3
Worksheets(strDataTab).Range("F9").Value = lngOthers4
Worksheets(strDataTab).Range("F10").Value = lngOthers5
Worksheets(strDataTab).Range("F11").Value = lngOthers6
Worksheets(strDataTab).Range("F12").Value = lngOthers7
Worksheets(strDataTab).Range("F13").Value = lngOthers8
End Sub

Private Function lngLastRow(ByVal strDataTab As String) As Long
Dim lngDataTotalRows As Long
lngDataTotalRows = 6
Do Until Worksheets(strDataTab).Cells(lngDataTotalRows, 3).Value = ""
lngDataTotalRows = lngDataTotalRows + 1
Loop
lngLastRow = lngDataTotalRows - 1
End Function

Private Function blnIsRegion(ByVal strDataTab As String, lngRowIndex As Long)
blnIsRegion = Worksheets(strDataTab).Cells(lngRowIndex, 3).Value = Worksheets(strDataTab).Range("E5").Value
End Function
[/vba]

[Bob Phillips]

In the input sheets, I have roughly 150 columns and 40,000 rows. The statement above checks for a certain attribute in a specified column. If there is a match, it is true and then it returns the count in a certain column. If not a match, it is false, and then returns the count in another column.

That much I could have said, it is just generic terms. I wanted to know what your business requirement is.

In my original post, I had 3 of these statements because my drop down menu takes values from 3 different columns. However, in the simplified example attached, it would look at Column 1 in the corresponding Data tab and if it is a matches the selection in cell E5, it returns the count in column E. If not a match, it returns the count in column F instead.

E5 holds AK, so if the AK numbers go in column E, the others in F, why do we have AK in row 12, the numbers can only be in E or F if I understand you, but you load numbers for both types in the code and then write them all back - I am confused by it all.

[Lawrence]

Does this help?

The idea is to know that for AK for example, 4 instances came from Division ABC, 1 from DEF, and 1 from XYZ, 2 from the East Region, etc...

but you load numbers for both types in the code and then write them all back - I am confused by it all.

I never said it was pretty, and there's probably a better way of doing it.

[Bob Phillips]

Every time you post something, you change it. We don't stand a chance.

Both sheets are different to how they were last time.

But, if I finally get it, you can do it with formula

E6: =SUMPRODUCT(--(('Prior Day-Data'!$A$1:$A$20=$C6)+('Prior Day-Data'!$B$1:$B$20=$C6)+('Prior Day-Data'!$C$1:$C$20=$C6)),--('Prior Day-Data'!$C$1:$C$20=E$5))
F6: =SUMPRODUCT(--(('Prior Day-Data'!$A$1:$A$20=$C6)+('Prior Day-Data'!$B$1:$B$20=$C6)+('Prior Day-Data'!$C$1:$C$20=$C6)))-E6

Copy E6:F6 down to row 13

E14: =SUMPRODUCT(--('Prior Day-Data'!$C$1:$C$20=$E$5),--('Prior Day-Data'!$D$1:$D$20<1000))
F14: =COUNTIF('Prior Day-Data'!D:D,"<1000")-E14

E15: =SUMPRODUCT(--('Prior Day-Data'!$C$1:$C$20=$E$5),--('Prior Day-Data'!$D$1:$D$20>=1000))
F15: =COUNTIF('Prior Day-Data'!D:D,">=1000")-E15