Macro recording: replace specific sheet name into active sheet

[valmir]

Hello everyone,
I have this macro recording:
Sub CondFormat_F5_P48()
'
' CondFormat_F5_P48 Macro
'

'
Range("B1").Select
Windows("PERSONAL.XLSB").Activate
Range("F5:P48").Select
Selection.Copy
Windows("Stats 1999.xlsx").Activate
Range("F5").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("H48,J48,L48,N48,P48").Select
Range("P48").Activate
Selection.NumberFormat = "-#"
Range("B1").Select
End Sub

Everytime I want to use it, I have to change the specific sheet name (in this case "Stats 1999") into whatever sheet name I'm using.
So, how can I make it work with whatever sheet is active at the moment?
Thanks!

 

[dave3009]

I would be careful with this being in your personal macro workbook and referring to activesheet, if you inadvertently trigger the code on a workbook it isn't intended for then you may mess up your work.

WIth that caveat out the way, try:

Sub CondFormat_F5_P48()
'
' CondFormat_F5_P48 Macro
'

'
Workbooks("PERSONAL.XLSB").Sheets(1).Range("F5:P48").Copy
With ActiveSheet
    .Range("F5:P48").PasteSpecial Paste:=xlPasteFormats
    .Range("H48,J48,L48,N48,P48").NumberFormat = "-#"
    .Range("B1").Select
End With
End Sub

 

[valmir]

I would be careful with this being in your personal macro workbook and referring to activesheet, if you inadvertently trigger the code on a workbook it isn't intended for then you may mess up your work.

WIth that caveat out the way, try:

Sub CondFormat_F5_P48()
'
' CondFormat_F5_P48 Macro
'

'
Workbooks("PERSONAL.XLSB").Sheets(1).Range("F5:P48").Copy
With ActiveSheet
    .Range("F5:P48").PasteSpecial Paste:=xlPasteFormats
    .Range("H48,J48,L48,N48,P48").NumberFormat = "-#"
    .Range("B1").Select
End With
End Sub

First of all, thanks for your prompt reply. And thanks for the warning, I am aware that I need to be careful otherwise I may mess up with whatever sheet is active at the moment!