(Solved) – Loop Excel Save As

(solved)-–-loop-excel-save-as

good day!

I am having a hard time trying to run this code. My objective is to save as the master file (“data entry”) and have an filename extension based on another excel file (“Book1”). Here’s my code:

Sub SaveAsLoop()

Dim wkb As Workbook
Dim fp, en, strName As String
Dim cRng, c as Range


Set cRng = Sheet1.Range("A1",Range("A121").End(xlup))
For Each c In cRng
strName = c.Value 

Set wkb = Workbooks.Open("C:UsersDesktopWFHdata entry.xlsm")

fp = "C:UsersDesktopWFH"
mfn = "data entry - "
en = "xlsm"

wkb.SaveAs Filename:=fp & mfn & strName & en, FileFormat:=52

ActiveWorkbook.Close

Next c

End Sub

Cell A1 to cell A121 of Book1 contains 121 countries and I want to create 121 copies of data entry.xlsm and have an extension based on cell reference. For ex;

Sheet1
A1   | Afghanistan
A2   | Algeria
...    ...
A121 | Serbia

And the output should be 121 excel files with filename extension like “data entry – Afghanistan”, “data entry – Algeria”, … , “data entry – Serbia”.

The problem is, the loop is not working and only work one time, the output only is 1 file with the file name using the cell A1 (“data entry – Afghanistan”).

Hope you guys can help me. Thanks in advance, stay safe!

Leave a Reply

Your email address will not be published. Required fields are marked *