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!