Best way to VBA code an identical copy of a sheet containing specific formatting

lysander

New Member
Joined
Dec 5, 2017
Messages
15
Hi

I would like to add to a macro that I'm writing specific formatting (grids/colors etc) that I made once upon a time for a table I use in excel. Rather than having to try and replicate my steps while recording code for the macro is there a way I can have this formatting coded coded automatically? I could then just copy/paste code into macro. I do not want the macro to be dependent on any other workbook.

Thanks
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

BrianJN1

Well-known Member
Joined
Jun 15, 2017
Messages
603
Hi

I would like to add to a macro that I'm writing specific formatting (grids/colors etc) that I made once upon a time for a table I use in excel. Rather than having to try and replicate my steps while recording code for the macro is there a way I can have this formatting coded coded automatically? I could then just copy/paste code into macro. I do not want the macro to be dependent on any other workbook.

Thanks

I doubt that this exactly meets what you require. I just used the Developer Macro editor to this. I selected a particular sheet, copied it in its entirety, and then used Paste-Formatting. The layout was exactly the same (but no values or formulae).
Code:
    Cells.Select
    Selection.Copy
    Sheets("Sheet1").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
 

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
Here are two Macro's that will do what you want I think!

The first Macro copies everything including values, formulas, formatting etc.
The second Macro copies ONLY the formatting etc.
Change the Sheets("New") to whatever sheet name you are copying the formatting etc to and make sure that you are in the original sheet when you run whichever Macro is suitable.

Macro 1:
Code:
Sub Copy_ALL()
    With Application
        .ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
    End With
    Cells.Copy
    Sheets("New").Select
    ActiveSheet.Paste
    Range("A1").Select
    With Application
        .DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
    End With
End Sub

Macro 2:
Code:
Sub Copy_Formatting()
    With Application
        .ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
    End With
    Cells.Copy
    Sheets("New").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A1").Select
    With Application
        .DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
    End With
End Sub

I hope this helps!
 

mrhstn

Active Member
Joined
Jul 25, 2017
Messages
316
An alternative might be to make a copy of the full 'template' sheet and empty the cell contents of the new one or have a template with no content hidden away which can be copied as needed

Code:
Sheets("Sheet3").Copy after:=Sheets(Sheets.Count)
ActiveSheet.UsedRange.ClearContents

It would be better to choose a definitive range rather than UsedRange.
 
Last edited:

lysander

New Member
Joined
Dec 5, 2017
Messages
15

ADVERTISEMENT

Thanks for the replies. However I am not sure how this gives me the code I need for a macro I'm writing in order for me not to have to record my formatting steps or write formatting code from scratch.
thanks
 

mrhstn

Active Member
Joined
Jul 25, 2017
Messages
316
The presumption is you frequently create a sheet with a specific design and format set, hence you want to automate the creation of said sheet.

If you create a template, free from any content other than formatting. Hide it out the way. Use a macro to make a copy as and when you need it. No need to write code to set the formatting ever again.
 

lysander

New Member
Joined
Dec 5, 2017
Messages
15
Ok I was a bit slow on this one. I have my template now saved as a hidden sheet in the workbook where I keep my macro. The code that S.H.A.D.O. provided works great with a little tweak. Had to switch these two lines around:
ActiveSheet.Paste
Range("A1").Select

Thanks everyone.
 

lysander

New Member
Joined
Dec 5, 2017
Messages
15
ohh I now have another little issue. I have formulas in my template as well. The issue is that these formulas refer to cell ranges in different sheets in my workbook where I copy in my template. The formulas are set up in my template to call the correct cell references based on how my sheets are named. However even when I refresh the formulas just return #VALUE ! They are not understanding the reference now they are copied over to a new workbook.

Any ideas what I can do. My formulas are relatively simple e.g.

=COUNTIF(LOCAL!$C$2:$C$145,"Scheduled")

=AVERAGEIF(LOCAL!$C$2:$C$24,"Scheduled",LOCAL!$I$3:$I$14)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,980
Messages
5,599,162
Members
414,295
Latest member
Dolenhil

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