VBA Copy and Paste only format

[x_swinson_x]

I need a code that will take the range of cells from one spread sheet and paste only the formatting to the current sheet.

Here's what I have:

Worksheets("Format").Range("B15:AF39").Copy
Copy:=xlCopyFormats


Worksheets("Sheet1").Range("B15:AF39").PasteSpecial
Paste:=xlPasteFormats

I'm not very good at VBA coding can someone tweak this to make it function correctly?

excel 2015 windows 7

 

[Takae]

Recorded macro.

Sub xx()
    Worksheets("Format").Range("B15:AF39").Copy
    Worksheets("Sheet1").Range("B15:AF39").PasteSpecial _
    Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

 

[My Aswer Is This]

Try this:

Sub Copy_Me()
Worksheets("Format").Range("B15:AF39").Copy
Worksheets("Sheet1").Range("B15:AF39").PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub

 

[x_swinson_x]

Try this:

Sub Copy_Me()
Worksheets("Format").Range("B15:AF39").Copy
Worksheets("Sheet1").Range("B15:AF39").PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub

This works great but I have multiple buttons on separate pages is there a way to tweak the code so instead of "Sheet1" it would run on the current sheet? So if I'm in sheet3 and I run the macro it will run on that sheet instead of creating seperate macros for each sheet in the workbook?

 

[My Aswer Is This]

Try this:

Sub Copy_Me()
'Version2
Worksheets("Format").Range("B15:AF39").Copy
ActiveSheet.Range("B15:AF39").PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub