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

 

[kvsrinivasamurthy]

Are the ranges, named ranges or tables?

 

[kvsrinivasamurthy]

Are the ranges, named ranges or tables?
If they are named ranges this code works on activesheet ranges.

Sub ResetFormat()
'
' ResetFormat Macro
' Rest conditional Fomatting
'
On Error Resume Next
Dim nm, T&
    Sheets("Ivory").Select
    Range("B10:T10").Select
    Selection.Copy
    
    For Each nm In ActiveWorkbook.Names
    If nm.RefersToRange.Parent.Name = ActiveSheet.Name Then
    Range(nm.Name).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    End If
    Next nm
    Range("L5").Select
End Sub

 

[Ramadan]

@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 foirst 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

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

@Kvsr
[/QUOTE]

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

@kvsrinivasamurthy , thank you bro for your effort but i got this below error

 

[Ramadan]

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

yes i have it as table name

 

[Ramadan]

Are the ranges, named ranges or tables?
If they are named ranges this code works on activesheet ranges.

Sub ResetFormat()
'
' ResetFormat Macro
' Rest conditional Fomatting
'
On Error Resume Next
Dim nm, T&
    Sheets("Ivory").Select
    Range("B10:T10").Select
    Selection.Copy
 
    For Each nm In ActiveWorkbook.Names
    If nm.RefersToRange.Parent.Name = ActiveSheet.Name Then
    Range(nm.Name).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    End If
    Next nm
    Range("L5").Select
End Sub

this

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

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

@MARK858 wow this seems to be perfect and very fast bro. but it paste the formatting also over my table headers starting from "B7" not "B10"

 

[Alex Blakenburg]

Maybe:

Sub CopyFormating()
    
    Dim srcWS As Worksheet
    
    Set srcWS = Worksheets("Ivory")
    
    srcWS.ListObjects(1).ListRows(1).Range.Copy
    ActiveSheet.ListObjects(1).DataBodyRange.PasteSpecial Paste:=xlPasteFormats
    
End Sub

 

[Ramadan]

Maybe:

Sub CopyFormating()
   
    Dim srcWS As Worksheet
   
    Set srcWS = Worksheets("Ivory")
   
    srcWS.ListObjects(1).ListRows(1).Range.Copy
    ActiveSheet.ListObjects(1).DataBodyRange.PasteSpecial Paste:=xlPasteFormats
   
End Sub

@Alex Blakenburg that's it. very simple and fast thank you soooooo much Alex

 

[Alex Blakenburg]

The others flushed out the background information required , so it was a joint effort

Just keep in mind that Programming / VBA is very specific and we need to know things like sheet names and where in the sheet is the data and is it in a table or not and what is the table name.
If you can provide that up front and some sample data or at least an image which includes the Row and Column reference, you will most likely get a solution much quicker.

 

[MikeVol]

Option Explicit

Sub ResetFormat_v2()
    Dim i           As Long
    Dim targetRng   As Range

    Dim ws          As Worksheet
    Set ws = ThisWorkbook.Worksheets("Ivory")

    Dim rngNames    As Variant
    rngNames = Array("Rnge1", "Rnge2", "Rnge3", "Rnge4")

    Dim rng         As Range
    Set rng = ws.Range("B10:T10")

    For i = LBound(rngNames) To UBound(rngNames)
        On Error Resume Next
        Set targetRng = ws.Range(rngNames(i))
        On Error GoTo 0

        If Not targetRng Is Nothing Then
            rng.Copy
            targetRng.PasteSpecial Paste:=xlPasteFormats
            Set targetRng = Nothing
            Application.CutCopyMode = False
        Else
            MsgBox "Error: Range " & rngNames(i) & " not found! ", vbCritical
            '            Debug.Print "Error: Range " & rngNames(i) & " not found! "
        End If

    Next i

    Application.Goto ws.Range("L5")
    Set rng = Nothing
    rngNames = Array()
    Set ws = Nothing
End Sub