How to run a macro for different sheets individually

[Ramadan]

I have a workbook with multiple sheets with the same tables design and conditional formatting, and because sometime I face some drops in applying conditional format is some sheets, so I recorded a macro to copy the first row from a sheet that I see that it's stable "Ivory" and paste only formatting to the full table in other sheet but the probelm is that the code only run for the this sheet only and i need to add a button in each sheet to run the code in case I noticed that condtional formatting is not applied in a proper way but not for all sheets at the same time only the active sheet. here is the code below but I don't know how to add (This Activesheet ) instead of the sheet name. please I need your help to edit that or if you have any other suggestions

Sub ResetFormat()
'
' ResetFormat Macro
' Rest conditional Fomatting
'

'
    Sheets("Ivory").Select
    Range("B10:T10").Select
    Selection.Copy
    Sheets("Kingsrange").Select
    Range("B10:T10").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("Kingsrange").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("L5").Select
End Sub

 

[JohnDM]

If "Kingsrange" is the Sheet you want to re-activate after copying cells in "Ivory" then you could use

Option Explicit

Sub ResetFormat()
'
' ResetFormat Macro
' Rest conditional Fomatting
'

'
    Dim wks As Worksheet
    Set wks = ActiveSheet
    Sheets("Ivory").Select
    Range("B10:T10").Select
    Selection.Copy
    wks.Select
    Range("B10:T10").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("Kingsrange").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("L5").Select
End Sub

This should then work for any Sheet that is the 'active Sheet' at the time the code runs.

 

[JohnDM]

Sorry, and delete the remaining line with

Range("Kingsrange").Select

 

[Ramadan]

If "Kingsrange" is the Sheet you want to re-activate after copying cells in "Ivory" then you could use

Option Explicit

Sub ResetFormat()
'
' ResetFormat Macro
' Rest conditional Fomatting
'

'
    Dim wks As Worksheet
    Set wks = ActiveSheet
    Sheets("Ivory").Select
    Range("B10:T10").Select
    Selection.Copy
    wks.Select
    Range("B10:T10").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("Kingsrange").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("L5").Select
End Sub

This should then work for any Sheet that is the 'active Sheet' at the time the code runs.

@JohnDM , No this is not I want. What I need is only to copy the first row from sheet "Ivory" then paste its formatting to all the table in the active sheet forget about "kingsrange" it's only an example from the sheet that I recorded the macro on it, it can be any other sheet so I need to add (ThisActivesheet) instead of "kingsrange" to be ble to run the code for any sheet i want

 

[kvsrinivasamurthy]

Try . This code will ask to enter the name of the sheet where you have to paste the format.

Sub ResetFormat()
'
' ResetFormat Macro
' Rest conditional Fomatting
'

Dim S$
S = InputBox("Enter the Name os the sheet to paste the format: ", "PASTE SHEET")
    Sheets("Ivory").Select
    Range("B10:T10").Select
    Selection.Copy
    Sheets(S).Select
    Range("B10:T10").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("Kingsrange").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("L5").Select
End Sub

 

[abdelfattah]

@Ramadan
not really sure what you want, but try this.

Sub ResetFormat()
Dim ws As Worksheet
Set ws = Sheets("ivory")
ws.Range("B10:T10").Copy ActiveSheet.Range("l5")
End Sub

 

[Ramadan]

Try . This code will ask to enter the name of the sheet where you have to paste the format.

Sub ResetFormat()
'
' ResetFormat Macro
' Rest conditional Fomatting
'

Dim S$
S = InputBox("Enter the Name os the sheet to paste the format: ", "PASTE SHEET")
    Sheets("Ivory").Select
    Range("B10:T10").Select
    Selection.Copy
    Sheets(S).Select
    Range("B10:T10").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("Kingsrange").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("L5").Select
End Sub

@kvsrinivasamurthy nice to hear from you again bro. please forget about "kingsrange" it's only the sheet that I recorded the code for it but I have multiple sheets and all I need is to copy the first row from sheet "Ivory" B10:T10 then to select all the table range in the current active sheet and to paste only formating to this sheet that's all

 

[MARK858]

Sub ResetFormat()
 Sheets("Ivory").Range("B10:T10").Copy
 ActiveSheet.Range("B10").CurrentRegion.PasteSpecial Paste:=xlPasteFormats
 Application.CutCopyMode = False
End Sub

 

[kvsrinivasamurthy]

Do you have 'Kingsrange' sheet and also 'Kingsrange' Named range.

 

[kvsrinivasamurthy]

Try.

Sub ResetFormat()
'
' ResetFormat Macro
' Rest conditional Fomatting
'

Dim S, T&
'Ennter all the range names in this array
S = Array("Rnge1", "Rnge2", "Rnge3", "Rnge4")
    Sheets("Ivory").Select
    Range("B10:T10").Select
    Selection.Copy
    
    For T = 0 To UBound(S)
    Range(S(T)).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Next T
    
    Range("L5").Select
End Sub