changing from specific sheet name to active sheet in code

[tuktuk]

i have the following code

    Set PNExtract = Worksheets("Sheet1").Range("M1")
        PNExtract.Value = "Part Number"
        PNExtract.Font.Bold = True

i need to change the code from being specific to "Sheet1" to "ActiveSheet".....

i know this is pretty simple but i am struggling with it....thanks a 1,000,000.

Tuktuk

 

[Jonmo1]

You pretty much had your own answer in the title of the post

    Set PNExtract = ActiveSheet.Range("M1") 
        PNExtract.Value = "Part Number" 
        PNExtract.Font.Bold = True

 

[ADAMC]

ActiveSheet.Range("M1")

 

[tuktuk]

still needign help wiht the active sheet concept

after i changed the "Sheet1" to ActiveSheet i am getting an error when it calls the other sub to locate the PN.....any suggestions? ohh yeah i kept the old text, preceeding with a "'"..........thought i'd clarify to avoid confustion.

here are the specifics:

    With ActiveSheet
    'With Worksheets("Sheet1")
            .Range("M:M").NumberFormat = "General"
                With .Range("M2:M" & ActiveSheet.Range("H" & Rows.Count).End(xlUp).Row)
                'With .Range("M2:M" & Worksheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Row)
                        .Formula = "=getpn(ActiveSheet!I2)"
                        '.Formula = "=getpn(Sheet1!I2)"
                        .Value = .Value
                End With
            .Range("M:M").ColumnWidth = "14.86"
            .Range("M:M").Font.Size = 10
            
        End With

Here is the get PN sub:

Public Function getpn(r As String) As String
Dim m As Object
With CreateObject("vbscript.regexp")
    .Pattern = "P/N.*?\d[.| ]"
    If .test(r) Then
        Set m = .Execute(r)
        getpn = Mid(m(0), 5, Len(m(0)) - 5)
    End If
End With
End Function

 

[Norie]

tuktuk

I don't think anybody is suggesting you use ActiveSheet here.

.Formula = "=getpn(ActiveSheet!I2)"

Are you sure you even need a worksheet reference in the first place?

.Formula = "=getpn(I2)"

 

[tuktuk]

...........stiil learning every day......i guess you can't use ActiveSheet with a . Formula.....


good call though, it appears to be functioning properly without a worksheet reference.

thanks.

 

[Jonmo1]

maybe

.Formula = "=getpn('" & ActiveSheet.name & "'!I2)"