I've found how to loop through and paste, but getting the two to work together I'm having an issue with. Super new to VBA programming

This is the loop I'm using is below to apply some formatting macros - this does work.:

Sub RunCodeOnAllXLSFiles()
Dim ws As Worksheet
Dim MyFile As String
Dim lCount As Long
Dim i As Long
Dim wbResults As Workbook, wbCodeBook As Workbook

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ActiveWorkbook
Set ws = Sheets("Sheet1")

'Change path to suit

LastRow = Range("A65536").End(xlUp).Row
For i = i To LastRow
'Change path to suit
MyFile = "C:\Documents and Settings\aeasterl\Desktop\July 2013 Merit Planning\Split Sheets\" _
& ws.Range("A" & i) & "\" & ws.Range("B" & i) & "\" & ws.Range("C" & i)

With .FileSearch
.NewSearch
.LookIn = MyFile
.FileType = msoFileTypeExcelWorkbooks
.filename = "*.xls"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(filename:=.FoundFiles(lCount), UpdateLinks:=0)


Application.Run ("PERSONAL.XLS!HideColumns")
Application.Run ("PERSONAL.XLS!FixView")
Application.Run ("PERSONAL.XLS!SetDataValidations")

wbResults.Close SaveChanges:=True
Next lCount
End If
End With

On Error GoTo 0
Next i
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
End With

End Sub

What I want to do is then write a macro to run before the others that will insert rows in each of the 119 worksheets I have in that folder, look at my template (it can be open or closed) and copy the top 11 rows then paste in each of the 119 worksheets then do a "paintbrush" on the template worksheet (whole) and apply to the 119 worksheets.

Here is what I recorded - I just can't figure out how to change the "Adams Parts 05-08-2013.xls" to be whatever is actually open within the loop (wbResults) does not work.


Sub InsertRowsPasteHeader()
'
' InsertRowsPasteHeader Macro
' Macro recorded 5/9/2013 by Amanda Easterling
'

'
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
ChDir "C:\Documents and Settings\aeasterl\Desktop\Merit Database Stuff"
Workbooks.Open Filename:= _
"C:\Documents and Settings\aeasterl\Desktop\Merit Database Stuff\July 2013 Merit Planning Template.xls" _
, UpdateLinks:=0
Windows("Adams Parts 05-08-13.xls").Activate
Windows("July 2013 Merit Planning Template.xls").Activate
Windows("Adams Parts 05-08-13.xls").Activate
Range("A1").Select
ActiveCell.FormulaR1C1 = _
"='[July 2013 Merit Planning Template.xls]Template'!R1C1:R1C3"
Range("A2").Select
Windows("July 2013 Merit Planning Template.xls").Activate
Range("A2:B3").Select
Windows("Adams Parts 05-08-13.xls").Activate
ActiveCell.FormulaR1C1 = _
"='[July 2013 Merit Planning Template.xls]Template'!R2C1:R3C2"
Range("A3").Select
Windows("July 2013 Merit Planning Template.xls").Activate
Range("A4:D5").Select
Windows("Adams Parts 05-08-13.xls").Activate
Range("A4").Select
ActiveCell.FormulaR1C1 = _
"='[July 2013 Merit Planning Template.xls]Template'!R4C1:R5C4"
Range("A1:A6").Select
Range("A6").Activate
Selection.ClearContents
Windows("July 2013 Merit Planning Template.xls").Activate
Rows("1:11").Select
Selection.Copy
Windows("Adams Parts 05-08-13.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Windows("July 2013 Merit Planning Template.xls").Activate
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("Adams Parts 05-08-13.xls").Activate
Cells.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub

This macro works when I change the worksheet name on a new workbook.

These files have to be separate workbooks as they get e-mailed out.


Thank you in Advance - My work isp prevents the upload of any file so I can't upload my workbook - but the macro recorded does have all the legit file and macro names in them.