I have some buttons on an Excel report. 2 in particular are for posting all Budget numbers to the report and posting the Actual numbers. I currently have 2 separate subs, one assigned to each button.
Public Sub PostAllActual()
Dim sheet As Worksheet
Dim rFind As Range
For Each sheet In ActiveWorkbook.Worksheets 'Cycle through all of the sheets
If sheet.Name <> "Index" Then 'Skip the Index sheet
'Search for the Index row in Col A
Set rFind = sheet.Cells.Find("Index", [H2], xlValues, xlWhole, xlByColumns, xlNext)
If rFind Is Nothing Then 'Did we find the Index row?
'Not Found - ORatio sheet
Else 'Found
PostNumbers sheet, 37, "I" 'Procedure to copy numbers to the YTD
End If
End If
Next sheet 'Move to the next sheet
Beep 'Auditory notification of completed process
End Sub
Public Sub PostAllBudgets()
Dim sheet As Worksheet
Dim rFind As Range
For Each sheet In ActiveWorkbook.Worksheets 'Cycle through all of the sheets
If sheet.Name <> "Index" Then 'Skip the Index Sheet
'Search for the Index row in Col A
Set rFind = sheet.Cells.Find("Index", [H2], xlValues, xlWhole, xlByColumns, xlNext)
If rFind Is Nothing Then 'Did we find the Index row?
'Not Found - ORatio sheet
Else 'Found
PostNumbers sheet, 21, "H" 'Procedure to copy the numbers to the YTD
End If
End If
Next sheet 'Move to the next sheet
Beep 'Auditory notification of completed process
End Sub
Do I really save anything by reducing these to simple sub calls on the button click, and passing in the 2 parameters like this?
Public Sub PostAllBudgets()
PostAllNumbers 21, "H"
End Sub
Public Sub PostAllActual()
PostAllNumbers 37, "I"
End Sub
Public Sub PostAllNumbers(ByVal iRow as Integer, ByVal sCol as String)
Dim sheet As Worksheet
Dim rFind As Range
For Each sheet In ActiveWorkbook.Worksheets 'Cycle through all of the sheets
If sheet.Name <> "Index" Then 'Skip the Index Sheet
'Search for the Index row in Col A
Set rFind = sheet.Cells.Find("Index", [H2], xlValues, xlWhole, xlByColumns, xlNext)
If rFind Is Nothing Then 'Did we find the Index row?
'Not Found - ORatio sheet
Else 'Found
PostNumbers sheet, iRow, sCol 'Procedure to copy the numbers to the YTD
End If
End If
Next sheet 'Move to the next sheet
Beep 'Auditory notification of completed process
End Sub