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

[lysander]

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

 

[BrianJN1]

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).

    Cells.Select
    Selection.Copy
    Sheets("Sheet1").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

 

[S.H.A.D.O.]

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:

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:

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]

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

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

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

 

[lysander]

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]

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]

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]

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)