changing from specific sheet name to active sheet in code

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
i have the following code
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
You pretty much had your own answer in the title of the post

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

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
Code:
ActiveSheet.Range("M1")
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
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:

Code:
    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:

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,328
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

tuktuk

I don't think anybody is suggesting you use ActiveSheet here.:)
Code:
.Formula = "=getpn(ActiveSheet!I2)"
Are you sure you even need a worksheet reference in the first place?
Code:
.Formula = "=getpn(I2)"
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
...........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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
maybe

Code:
.Formula = "=getpn('" & ActiveSheet.name & "'!I2)"
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,561
Messages
5,765,113
Members
425,261
Latest member
JW00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top