Opening a different workbook and running a macro

[HernusB]

Good day. This is my first time asking a question here.
I have a blank workbook that contains one macro.
The macro opens a template workbook in the same folder, changes the name of the file (from a list in the template workbook), saves it and then closes the template workbook.
It runs through the list until the template is saved as: all the names in the template.
The template workbook contains 3 x macros.
1 is to unprotect the sheets
2 is to rename some sheets with the new name from the list
3 is to protect the sheets

I've tried running the macros inside the template with no luck.
The moment it gets to running the first macro to unprotect the sheets, it has to be debugged.

Sub LoopMacro()
Dim savefilename As String
Dim thiswb As Workbook
Dim templatepath As String
Dim templatewb As Workbook
Dim templatewb1 As Workbook
Dim templatews1 As Worksheet
Dim templatews2 As Worksheet
Dim n As String
Dim i As Integer

Set thiswb = ThisWorkbook
templatepath = "D:\LOOPTEMPLATEMACRO\BOND TEMPLATE RECEIPTING.xlsm"
Set templatewb = Workbooks.Open(templatepath)
Set templatews1 = Sheets("HOW TO")
Set templatews2 = Sheets("Short Bond Names")

i = 1
n = Trim(templatews2.Range("A" & i).Value)
Application.ScreenUpdating = False

Do While n <> ""
Set templatewb = Workbooks.Open(templatepath)
Set templatews1 = Sheets("HOW TO")
Set templatews2 = Sheets("Short Bond Names")

'this is the first macro that unprotects the sheets
Application.Run "'" & templatewb & "'!unprotallshts"

n = Trim(templatews2.Cells(i + 1, 1).Value)
templatews2.Range("A" & i).Copy templatews1.Range("V11")
templatews2.Range("B" & i).Copy templatews1.Range("AB11")
templatews2.Range("C" & i).Copy templatews1.Range("V12")

'this is the first macro that renames the sheets
Application.Run "'" & templatewb & "'!rename_sheets"

'this is the first macro that protects the sheets
Application.Run "'" & templatewb & "'!protallshts"

savefilename = templatews1.Range("V2")

With templatewb
.SaveAs Filename:=savefilename
.Close
End With

i = i + 1


Loop
Application.ScreenUpdating = True
Application.Quit
End Sub

 

[jmacleary]

Hi there. I have had a quick look, and you will need to qualify all the various sheet references with the workbook name as well. So for instance you have

Set templatewb = Workbooks.Open(templatepath)
    Set templatews1 = Sheets("HOW TO")

The second line would need to be

Set templatews1 = templatewb.Sheets("HOW TO")

and so on.

 

[HernusB]

Hi, thank you for the reply. I tried it but unfortunately it didn't work. It still bugs out on the Application.Run line

 

[Norie]

What's the error message?

 

[HernusB]

It doesn't give an error. It just opens the VBA screen and highlights the Applaction.Run in yellow

 

[Norie]

What happens if you then press F5 or F8?

 

[HernusB]

I'll check F5 and F8 tomorrow at work. Thanx