Convert Array name to String

[MSEconstudent]

Hello all,

So I have a number of arrays in this particular macro and my code works well except for when I need to save as different file names. I need to save the files using the names MW_1, MW_2, NE, etc. Below is a small selected bit of my code. It will not take the reg in the naming convention for the file because it is a variant. I just need to convert the reg array name to a string so rather than VBA recognizing MW_1 as that array I just want it to take MW_1 as a string and allow it in the naming convention. Thoughts? I initially tried using Join but due to the nature of using two arrays Join gives me the list IAILKSMI, for example, in the file name. I also tried using Cstr() but that doesn't work either.

MW_1 = Array("IA", "IL", "IN", "KS", "MI")
MW_2 = Array("MN", "MO", "ND", "NE", "OH", "SD", "WI")
NE = Array("CT", "MA", "ME", "NH", "NJ", "NY", "PA", "RI", "VT")
SO_1 = Array("AL", "AR", "DC", "DE", "FL", "GA")
SO_2 = Array("KY", "LA", "MD", "MI", "NC", "OK", "SC")
SO_3 = Array("TN", "TX", "VA", "WV")
WE_1 = Array("AK", "AZ", "CA", "CO", "HI")
WE_2 = Array("ID", "MT", "NM", "NV", "OR", "UT", "WA", "WY")

regions = Array(MW_1, MW_2, NE, SO_1, SO_2, SO_3, WE_1, WE_2)

For Each reg In regions
        ActiveWorkbook.SaveAs Filename:=root1 & reg & "_" & con & "_RANK.xls"
        Application.DisplayAlerts = True
        ActiveWorkbook.Close
Next

 

[Norie]

Perhaps something like this.

Dim I As Long

    regions = Array("MW_1", "MW_2", "NE", "SO_1", "SO_2", "SO_3", "WE_1", "WE_2")

    For I = LBound(regions) To UBound(regions)

        ActiveWorkbook.SaveAs Filename:=root1 & regions(I) & "_" & con & "_RANK.xls"
        Application.DisplayAlerts = True
        ActiveWorkbook.Close

    Next I

 

[MSEconstudent]

Thanks for the reply. This would work in general in terms of looping through and saving, my issue is that this particular bit of code takes place within 2 loops where regions must remain an array of arrays. As shown below. I believe if I do the code above it is going to correctly save filenames but it would just save my first generated file over and over again under those various string names.

MW_1 = Array("IA", "IL", "IN", "KS", "MI")
MW_2 = Array("MN", "MO", "ND", "NE", "OH", "SD", "WI")
NE = Array("CT", "MA", "ME", "NH", "NJ", "NY", "PA", "RI", "VT")
SO_1 = Array("AL", "AR", "DC", "DE", "FL", "GA")
SO_2 = Array("KY", "LA", "MD", "MI", "NC", "OK", "SC")
SO_3 = Array("TN", "TX", "VA", "WV")
WE_1 = Array("AK", "AZ", "CA", "CO", "HI")
WE_2 = Array("ID", "MT", "NM", "NV", "OR", "UT", "WA", "WY")

regions = Array(MW_1, MW_2, NE, SO_1, SO_2, SO_3, WE_1, WE_2)

For Each reg In regions
'--->formatting things
    For Each State In reg
'---> data import and manipulation of sheet
    Next
        Application.DisplayAlerts = False
        base.Copy
'---> Line immediately below needs to somehow get that reg to not be a variant and correspond to the correct string name
        ActiveWorkbook.SaveAs Filename:=root1 & reg & "_" & con & "_RANK.xls"
        Application.DisplayAlerts = True
        ActiveWorkbook.Close
Next

Thanks again for taking a look!

 

[MSEconstudent]

Actually I was able to use that code that was given with slight alterations and the program is now working, thanks! It was enough to get me going.