PDA

View Full Version : Solved: VBA Copy a template sheet and paste it into multiple sheets



d4y88
09-25-2012, 07:36 AM
Hi, I'm hoping somebody can help solve my problem.

I have a workbook with multiple sheets, i have a sheet "template" with a set format on that I would like to copy and paste into several other sheets. These sheets all have the word "AOB" at the end of their name, this macro is to be used by several users on different computers so I would like to avoid saving a template as an .xlt as this would cause problems on other machines.

Is there a way to loop through all sheets that contain "AOB" in their name and paste the template over the top?

Thanks in advance.

Jan Karel Pieterse
09-25-2012, 08:32 AM
Sure.


Sub CopyTemplate()
Dim oSh As Worksheet
Dim oTemplate As Worksheet
Set oTemplate = Worksheets("TemplateSheet")
For Each oSh In Worksheets
If Right(oSh.Name, 3) = "AOB" Then
oTemplate.UsedRange.Copy oSh.Range("A1")
End If
Next
End Sub

d4y88
09-25-2012, 08:43 AM
Worked a treat, thanks for your help.