CODEWITHSUNDEEP
excelvba
MitchMahoney
MitchMahoney

Reputation: 95

Running code on selected sheet rather than active sheet

I am setting my ws variable to the first worksheet but my code runs on the active sheet.

This code runs from a button on Sheet2 and I am using Sheets(1).Select as the name of the first worksheet can vary.

What do I need to change so that my code runs on Sheets(1)?

Function Test()

Dim ws As Worksheet
Dim lColumn As Long
Dim lRow As Long
Dim myRange As Range
Dim cell As Range

Set ws = Sheets(1)

    With ws
        lColumn = ws.UsedRange.Columns.Count
        lRow = Cells(Rows.Count, 2).End(xlUp).Row

        colName = Split(Worksheets(1).Cells(1, lColumn).Address, "$")(1)

        Range("A1: " & colName & "1").Font.Bold = True

        Set myRange = Range("A1: " & colName & "1")

        For Each cell In myRange
            cell.Interior.Pattern = xlSolid
            cell.Interior.PatternColorIndex = xlAutomatic
            cell.Interior.ThemeColor = xlThemeColorDark1
            cell.Interior.TintAndShade = -0.249977111117893
            cell.Interior.PatternTintAndShade = 0
        Next

    End with

End Function

Upvotes: 0

Views: 124

Answers (1)

xgg
xgg

Reputation: 159

When you use With statements, you still need to prefix . in front of the properties, so that the member call is made against the With block variable.

I changed lColumn = ws.UsedRange.Columns.Count to lColumn = .UsedRange.Columns.Count

.Range("A1: " & colName & "1").Font.Bold = True
Set myRange = .Range("A1: " & colName & "1")

by not using the . VBA thinks you are referencing the ActiveSheet.

Upvotes: 3

Related Questions