Using an array in another array

[Robert E Lee]

Hi All

I have a largish database which is updated monthly from various sources and which is contains details of members of 9 groups. After the update I then filter and produce 9 worksheets on a new workbook, the sheet names being 2 letter codes. I then use this code

Dim WB As Workbook, Wb2 As Workbook, Path As String
    Path = "C:Group"
    Dim GroupA() As String, i As Integer
    
                    Application.DisplayAlerts = False
                    Application.ScreenUpdating = False
                    
    GroupA() = Split("AZ|BQ|CF|DD|HK|PY|ST|VG|WW", "|", -1, vbBinaryCompare)
    
   Set WB = ActiveWorkbook
 For i = 0 To UBound(GroupA)
  
         WB.Sheets(GroupA(i)).Copy
             Set Wb2 = ActiveWorkbook
              Wb2.SaveAs Filename:=Path & GroupA(i)
                Wb2.Close
  Next i
WB.Close

What I would like to be able to do is to save the new workbooks, not named by the 2 letter code, but by the full name.

I am sure this can be done using a second array but just can't figure it out.

Any assistance welcome

Robert

[ajryan88]

Hi Robert,

Try this code in place of what you have above:

Dim WB As Workbook, Wb2 As Workbook, Path As String
Dim GroupA() As String, i As Integer
Dim GroupB() As String

Path = "C:Group"

GroupB() = Split("Person1|Person2|...|Person9", "|", -1, vbBinaryCompare)

Application.DisplayAlerts = False
Application.ScreenUpdating = False

GroupA() = Split("AZ|BQ|CF|DD|HK|PY|ST|VG|WW", "|", -1, vbBinaryCompare)

Set WB = ActiveWorkbook
For i = 0 To UBound(GroupA)
    
    WB.Sheets(GroupA(i)).Copy
    Set Wb2 = ActiveWorkbook
    Wb2.SaveAs Filename:=Path & GroupB(i)
    Wb2.Close
Next i

WB.Close

NB: I have not tested this code as I don't have your workbook, thus making it a bit difficult to guarantee that it will work.

However if this doesn't work, it won't be far off, so please don't hesitate to let me know and I'll work with you until it's resolved. This may also mean I will ask you to upload your workbook so that I can test a solution before suggesting it here.

Hope this helps

[Robert E Lee]

Thanks for that that is so much more simple and elegant than the tortuous loops I had got myself into. There does seem to be one minor problem in that it works perfectly until the last element of the array when I get an error message indicating that the path is incorrect. I'll have a closer look at it later and am sure the problem is with me and my element of the code.

Thanks again

Robert

[ajryan88]

No problems!

I wouldn't mind taking a look for you if you wouldn't mind uploading the workbook(s) required to make this macro run to error.

Thanks

[Robert E Lee]

Thanks again. As I suspected the problem lay with me; I had managed to put || between the penultimate and last settings for GroupB, which I discovered by checking the locals window which showed limits of (0 to 9) which led me to my error.

Everything now perfect and thanks for your interest

Robert

[ajryan88]

No worries, happy to help!

Please don't forget to mark this thread as solved and please click on the * next to my post to say thanks