copy template and/or values into multiple sheets

The problem I'm having is that I have this Excel Worksheet in one workbook which I want to use for a template, and I have to make all the other worksheets, which are like 19 workbooks. The template has a title of "blahblahblah" and it should appear in bold between cells B1 to K1 with the cells being merged and the font being bold. The template has a lot more information in it and I want to apply it to all the worksheets in all the workbooks. I got it so far to run through all the worksheets in all the workbooks, and I have tried everything I know how to get the cells to do something, but nothing happens. Here is the code I have:


Option Explicit
Public Sub WBFormat()

Dim strMasterFile As String
Dim strWBNames(19)
Dim strPath As String
Dim n As Integer
Dim objWS As Worksheet
Dim workname As Worksheet
Dim cell1, cell2, rng As Range
Dim bk As Worksheet

On Error Resume Next

'set master file name
strMasterFile = "template2.xls"

'set path to files (note closing "\")
strPath = "C:\Daddys_stuff\"

'setup file names
strWBNames(1) = "D1.xls"
strWBNames(2) = "D2.xls"
strWBNames(3) = "D3.xls"
strWBNames(4) = "D4.xls"
strWBNames(5) = "D5.xls"
strWBNames(6) = "J1.xls"
strWBNames(7) = "J2.xls"
strWBNames(8) = "J3.xls"
strWBNames(9) = "J4.xls"
strWBNames(10) = "J5.xls"
strWBNames(11) = "NoM.xls"
strWBNames(12) = "R1.xls"
strWBNames(13) = "R2.xls"
strWBNames(14) = "R3.xls"
strWBNames(15) = "R4.xls"
strWBNames(16) = "R5.xls"
strWBNames(17) = "R6.xls"
strWBNames(18) = "R7.xls"
strWBNames(19) = "R8.xls"


'Copy formatting from each worksheet in Copy_Master.xls to
'each worksheet in the named workbooks

'Set cell1 = bk.Cells(1, 2)
'Set cell2 = bk.Cells(12, 22)
'Set rng = Range(cell1, cell2)

For n = 1 To 19
workname = (strPath & strWBNames(n))
Workbooks.Open (strPath & strWBNames(n))

For Each objWS In workname
'If workname.Range("B1", "K1") <> " " Then
workname.Range("B1:K1").Offset(1, 0).Select
workname.Range("B1:K1").Value = "blahblahblah"
workname.Range("B1:K1").Merge
workname.Range("B1:K1").Font.Bold = True

'Else

'workname.Range("B1", "K1").Value = "blah,blah,blah"
'workname.Range("B1", "K1").Merge
'workname.Range("B1", "K1").Font.Bold = True

'End If


'Workbooks(strWBNames(n)).Worksheets(objWS.Name).Range("A1", "O12") _
.PasteSpecial Paste:=xlPasteFormats
Next objWS
'Workbooks(strWBNames(n)).Save
Next n
End Sub


I have commented some code out and this the best I know how to do for someone who has practically no experience in VBA. This has to be finished in a couple of days so I would appreciate your help.

I hope this helps - I am very new at this and VBA forums and I don't want to break any rules.

Please Login or Register  to view this content.