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

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
701
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
What is the error message & number you get?
 
Upvote 0
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".
 
Upvote 0
Try using
VBA Code:
Set s = Workbooks.Open("\\Location\DocumentTemplate.xlsx", False)
 
Upvote 0
Try using
VBA Code:
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:
VBA Code:
Set sMS1 = s.Worksheets("MS1")
I've already ensured that the tab is entitled MS1, so I'm at a loss.
 
Upvote 0
Upvote 0
Glad it's sorted & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top