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

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,850
Office Version
  1. 365
Platform
  1. Windows
What is the error message & number you get?
 

reberryjr

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,850
Office Version
  1. 365
Platform
  1. Windows
Try using
VBA Code:
Set s = Workbooks.Open("\\Location\DocumentTemplate.xlsx", False)
 

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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.
 

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,850
Office Version
  1. 365
Platform
  1. Windows
Glad it's sorted & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,173
Members
417,129
Latest member
geekzilla

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
Top