Dynamic List for File Save Array

[chipsworld]

Happy New year! Had a quick question.

How can I accomplish the below but from a dynamic list instead of putting the names in the code?

arrWBS = Array("JESSICA", "FLEET", "****", "ALAN", "MASTER")
 
For i = LBound(arrWBS) To UBound(arrWBS)
       ThisWorkbook.SaveAs Filename:=pth & "BUY REPORT Ver" & Format(Now(), "yymm") & " v2.4 - " & arrWBS(i) & ".xlsm"

Next i

 

[Joe4]

Where exactly will this list be located?

 

[dmt32]

Happy New year! Had a quick question.

Happy New Year - here is a quick answer

Put the names in a Table (Listobject) & read the databodyrange in to an array

Untested but something like this

    Dim tbl         As ListObject
    Dim FileName    As Variant, FileNames
  
    Set tbl = Worksheets("Sheet1").ListObjects(1)
    FileNames = tbl.DataBodyRange.Value2
  
    For Each FileName In FileNames
        ThisWorkbook.SaveAs FileName:=pth & "BUY REPORT Ver" & Format(Now(), "yymm") & " v2.4 - " & FileName & ".xlsm"
    Next FileName

Hope helpful

Dave

02-01-2025.xls
	A
1	File Name
2	JESSICA
3	FLEET
4	ALAN
5	MASTER
Sheet1

 

[chipsworld]

Happy New Year - here is a quick answer

Put the names in a Table (Listobject) & read the databodyrange in to an array

Untested but something like this

    Dim tbl         As ListObject
    Dim FileName    As Variant, FileNames
 
    Set tbl = Worksheets("Sheet1").ListObjects(1)
    FileNames = tbl.DataBodyRange.Value2
 
    For Each FileName In FileNames
        ThisWorkbook.SaveAs FileName:=pth & "BUY REPORT Ver" & Format(Now(), "yymm") & " v2.4 - " & FileName & ".xlsm"
    Next FileName

Hope helpful

Dave

02-01-2025.xls
	A
1	File Name
2	JESSICA
3	FLEET
4	ALAN
5	MASTER
Sheet1

Dave,
Had a glitch...

Run-TIme error 13 "Type Mismatch" on line - " ThisWorkbook.SaveAs FileName:=pth & "BUY REPORT Ver" & Format(Now(), "yymm") & " v2.4 - " & FileName & ".xlsm""

 

[chipsworld]

Dave,
Had a glitch...

Run-TIme error 13 "Type Mismatch" on line - " ThisWorkbook.SaveAs FileName:=pth & "BUY REPORT Ver" & Format(Now(), "yymm") & " v2.4 - " & FileName & ".xlsm""

Skip the glitch! Thank you for the "Quick Answer"!

Have a great week!

 

[dmt32]

Skip the glitch! Thank you for the "Quick Answer"!

Have a great week!

Appreciate your feedback & glad we were able to help

In my haste in posting I overlooked that you had not specified the FileFormat

ThisWorkbook.SaveAs FileName:=pth & "BUY REPORT Ver" & Format(Now(), "yymm") & " v2.4 - " & FileName & ".xlsm", FileFormat:=52

Excel 2007 > when using SaveAs method always best to provide both the FileFormat parameter and correct File extension

Dave