How to copy excel sheet with formatting to dynamically created excel files?

So i have an excel file with a template sheet which i would need to copy to all the dynamically created excel files which have similar name: “Request for approval - xxxx”, where xxxx changes dynamically. Is there any way to do it so the formatting and colors are also copied or?

Depending on your requirements I can think of two possible solutions.

Is there a way you can copy the template file to create the request for approval xxxx file? This way you don’t have to worry about transferring formatting etc.

The solution I would use is to create a macro to do the copying of the template rather than using UI path, this way you can specify to copy the formatting as well rather than just the data. You can use an array in the macro similar to in UI path so that all the request for approval files are processed regardless of their xxxx value.

Is there any example you could reference me to because i’m new to VBA and i can’t seem to find anything similar?

Easiest way to learn VBA is to use the macro recorder and emulate the steps you want the macro to take.

Then you can look at the code produced and adjust it to your requirements.

hi @mxs5052,

Yes @KEntwistle is right . like this

Sub CopySheet()
Worksheets("sourceSheet").Copy After:=Worksheets("Sheet1")
End Sub  


@KEntwistle & @balupad14 thank you for your help, i will try with first with the recorder and let u know if i make it

1 Like