Loop Through Range, Copy and Paste Values, Save As and Continue

[reberryjr]

I'm trying to copy and paste a customer number from cell A2, into a template (different document). The template performs various VLOOKUPs against the same document that the customer number came from. Then, I want to save the template as the customer's id (the value that is now in cell K7 on the template), and move onto the next customer number (A3, then A4, etc.); until the end of the file.

For some reason, I'm getting an error at the font in red. I'm not sure why, as I've used the same code in a different Sub elsewhere.

Sub CreateDocuments()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m, s As Workbook
Dim mCD, mTH, sMS1 As Worksheet
Dim mCDLR As Long
Dim Rng, c As Range
Dim fPath, fName As String

Set m = ThisWorkbook
Set mCD = ThisWorkbook.Sheets("Core_Data")
Set mTH = ThisWorkbook.Sheets("TranHist")
Set s = Workbooks.Open("\\Location\DocumentTemplate.xlsx")
[COLOR=rgb(226, 80, 65)]Set sMS1 = s.Worksheets("MS1")[/COLOR]

mCDLR = mCD.Range("A" & Rows.Count).End(xlUp).Row

Set Rng = mCD.Range("A2:A" & mCDLR)

fPath = "\\Location\Today's Documents\"

'fName = sws.Range("K7").Value & ".xlsx"

For Each c In Rng
fName = sMS1.Range("K7").Value & ".xlsx"
    If c <> "" Then
        c.Copy
        sMS1.Range("K7").PasteSpecial xlPasteValues
        s.SaveAs fPath & fName
    End If
Next


Application.DisplayAlerts = True
Application.DisplayAlerts = True

End Sub

 

[Fluff]

What is the error message & number you get?

 

[reberryjr]

What is the error message & number you get?

Firstly, I get a pop up saying that the workbook the code is opening has external links, asking if want to update them. I thought my DisplayAlerts = False would keep that from happening. The other error is just "Code Execution Has Been Interrupted".

 

[Fluff]

Try using

Set s = Workbooks.Open("\\Location\DocumentTemplate.xlsx", False)

 

[reberryjr]

Try using

Set s = Workbooks.Open("\\Location\DocumentTemplate.xlsx", False)

I made that change, and it seemed to prevent the pop up, but the code is still being "interrupted" at this line:

Set sMS1 = s.Worksheets("MS1")

I've already ensured that the tab is entitled MS1, so I'm at a loss.

 

[Fluff]

Ok have a look here & see if that solves the problem

Code Execution Has Been Interrupted- VBA Keeps Breaking – Solved! – Bonbon's Guide To Data & Tech

bonbonsguide.com

 

[reberryjr]

Ok have a look here & see if that solves the problem

Code Execution Has Been Interrupted- VBA Keeps Breaking – Solved! – Bonbon's Guide To Data & Tech

bonbonsguide.com

That worked. Hopefully it's a temporary issue/one time fix. I do have another issue with this same project, but since it's a different issue, I'll post a new thread.

 

[Fluff]

Glad it's sorted & thanks for the feedback.