Variable Assignment to another Worksheet - is there a Cleaner method

[sbrotz]

Looking for guidance to have declared variables placed in target cells on a different worksheet within the same workbook. The existing code is below (NumRows hold the number of active rows within the worksheet). While it gets the job done - any suggestions on how to Improve/simplify? THANKS for the view!!

Dim CallGoal As Long
Dim ApptGoal As Long
Dim SalesGoal As Long
Dim RevGoal As Long
CallGoal = Application.WorksheetFunction.Min(Range("W2:W" & NumRows))
ApptGoal = Application.WorksheetFunction.Min(Range("X2:X" & NumRows))
SalesGoal = Application.WorksheetFunction.Min(Range("Y2:Y" & NumRows))
RevGoal = Application.WorksheetFunction.Min(Range("Z2:Z" & NumRows))

Worksheets("Report").Select
Range("K5").Select
ActiveCell.Value = CallGoal
Range("K6").Select
ActiveCell.Value = ApptGoal
Range("K7").Select
ActiveCell.Value = SalesGoal
Range("K8").Select
ActiveCell.Value = RevGoal
Range("A1").Select

 

[JoeMo]

Assuming NumRows is defined and a assigned a value before this modification which is untested:

Sub test()
Dim Goals(1 To 4) As Variant, Ct As Long
'need some code to initiate NumRows here
Ct = 23
For i = 1 To 4
    Goals(i) = Application.WorksheetFunction.Min(Range(Cells(2, Ct), Cells(NumRows, Ct)))
    Ct = Ct + 1
Next i

With Worksheets("Report")
    .Range("K5:K8").Value = Application.Transpose(Goals)
    .Select
    .Range("A1").Select
End With
End Sub

 

[RickXL]

Hi,

If I am working with more than one worksheet I like to give them both internal names that VBA can use.
Also, I think Application.WorksheetFunction is far too long for easy reading so I like to shorten it to AWF.

So I would do it something like this:

Sub Demo1()

    Dim CallGoal As Long
    Dim ApptGoal As Long
    Dim SalesGoal As Long
    Dim RevGoal As Long
    Dim NumRows As Long
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim AWF As WorksheetFunction
    
    Set AWF = WorksheetFunction
    Set ws1 = ThisWorkbook.Worksheets("Sheet2")
    Set ws2 = ThisWorkbook.Worksheets("Report")
        
    With ws1
        NumRows = .Cells(.Rows.Count, "W").End(xlUp).Row
        CallGoal = AWF.Min(.Range("W2:W" & NumRows))
        ApptGoal = AWF.Min(.Range("X2:X" & NumRows))
        SalesGoal = AWF.Min(.Range("Y2:Y" & NumRows))
        RevGoal = AWF.Min(.Range("Z2:Z" & NumRows))
    End With
    
    With ws2
        .Range("K5") = CallGoal
        .Range("K6") = ApptGoal
        .Range("K7") = SalesGoal
        .Range("K8") = RevGoal
        .Activate
        .Range("A1").Select
    End With
    
End Sub

One question would be: Do you actually need the internal variables like CallGoal etc at all? Because if you don't then this works as well:

Sub Demo2()

    Dim NumRows As Long
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim AWF As WorksheetFunction
    
    Set AWF = WorksheetFunction
    Set ws1 = ThisWorkbook.Worksheets("Sheet2")
    Set ws2 = ThisWorkbook.Worksheets("Report")
        
    With ws1
        NumRows = .Cells(.Rows.Count, "W").End(xlUp).Row
        ws2.Range("K5") = AWF.Min(.Range("W2:W" & NumRows))
        ws2.Range("K6") = AWF.Min(.Range("X2:X" & NumRows))
        ws2.Range("K7") = AWF.Min(.Range("Y2:Y" & NumRows))
        ws2.Range("K8") = AWF.Min(.Range("Z2:Z" & NumRows))
        ws2.Activate
        ws2.Range("A1").Select
    End With
    
End Sub

 

[RickXL]

Joe, I never thought of arrays. Nifty move

 

[sbrotz]

ADVERTISEMENT

Certainly less lines - Thanks for the quick view and replies.

Joe - I am a novice and do not understand the Ct = 23 statement. Can you shed some light?
Much cleaner - thanks....

 

[sbrotz]

Got it. Column W is 23. The next increments. Very nice - thank you....

 

[JoeMo]

Got it. Column W is 23. The next increments. Very nice - thank you....

Good work!

Glad I could help and thanks for the reply.