Ndtyjky

I have some vArrays which are not clearing out. The purspose of the macro is to work on a raw data tab which has 30+ tabs, each tab holding information for a specific office, 001-New York, etc. The macro is supposed to select x number of tabs (based on a reference file), copy them and save them into a new workbook. The problem is that instead of copying and saving from the raw data file it save the reference file instead. A For...Next loop is used to determine which tabs/offices to select & copy from the raw data file. The varrays are inside the loop and contain the names of the offices. When the code encounters the vArray the varray values are not clearing out when the loop circles back around.

Example:

'For 1' reference a cell with value of "8" so it populates 8 different vArray values (offices in this case). 'For 2' has a reference number of 5 and is supposed to populate 5 vArray values. It does this correctly as I can see the 5 new values in the locals window under vArray (1) thru vArray (5), however, vArray 6 thru 8 are showing values of the previous loop instead of 'Empty'. The vArray values are not clearing out when the macro loops.

sMasterListWBName is the reference file which tells the macro which tabs to copy from the raw data file and where to move the newly created workbook. The sub is also copying, saving, and distributing the reference file instead of the raw data file for some iterations of the loop (secondary issue--I will try to refrain from splitting the thread topic).

Thanks in advance to anyone who tries to answer this question.

Option Explicit

Dim iYear As Integer, iMonth As Integer, iVer As Integer, icount As Integer, iCount2 As Integer

Dim iLetter As String, iReport As String

Dim sMonth As String, sDate As String, sVer As String, sAnswer As String

Dim sFolderName As String, sManagerInitials  As String

Dim iManagerNumber As Integer, iManagerStart As Integer, iTabNumber As Integer, iTabStart As Integer

Dim sMasterListWBName As String, sConsolidatedWBName As String, sExists As String

Dim oSheet As Object, oDistList As Object

Dim vArray(300) As Variant

Dim wbDistList As Workbook

Dim wsAgentListSheet As Worksheet, wsMain As Worksheet

Dim rCell As Range, rCell2 As Range, rCellTotal As Range

Public sFINorAgent As String





Sub Agent_Distribute()

On Error Resume Next



iYear = frm_fin_rep_main_distribute.txt_year

iMonth = frm_fin_rep_main_distribute.txt_month

iVer = frm_fin_rep_main_distribute.txt_version



sMonth = Right("0" & iMonth, 2)

sDate = iYear & "." & sMonth



sVer = "V" & iVer

sAnswer = MsgBox("Is the following information correct?" & vbNewLine & vbNewLine & _

                    "Report - " & frm_fin_rep_main.sLetter & vbNewLine & _

                    "Year - " & iYear & vbNewLine & _

                    "Month - " & sMonth & vbNewLine & _

                    "Name - " & frm_fin_rep_main.sReport & vbNewLine & _

                    "Version - " & sVer, vbYesNo + vbInformation, "Please verify...")

If sAnswer <> vbYes Then

    Exit Sub

End If



Unload frm_fin_rep_main_distribute

frm_agent.Hide

Form_Progress



With Application

    .EnableEvents = False

    .ScreenUpdating = False

End With



sConsolidatedWBName = ActiveWorkbook.Name

sMasterListWBName = "Dist Master List Final.xls"

If Not IsFileOpen(sMasterListWBName) Then

    Workbooks.Open FileName:= _

    "W:Addins1 GL - Distribution" & sMasterListWBName, Password:="password"

    Workbooks(sConsolidatedWBName).Activate

End If



Set oDistList = Workbooks(sMasterListWBName).Worksheets("Agent")



With oDistList



    iManagerNumber = .Range("ManNumber2") 'range value = 66



    For iManagerStart = 2 To iManagerNumber '2 to 66

        If .Range("A" & iManagerStart) = "x" Then



            iTabNumber = .Range("E" & iManagerStart) 'E2 to E66

            sFolderName = .Range("F" & iManagerStart) 'F2 to F66

            sManagerInitials = .Range("G" & iManagerStart) 'G2 to G66



                For iTabStart = 1 To iTabNumber

                    vArray(iTabStart) = .Range("G" & iManagerStart).Offset(0, iTabStart)

                Next iTabStart



                If iTabNumber = 1 Then

                    Sheets(vArray(1)).Select

                Else

                    Sheets(vArray(1)).Select

                    For iTabStart = 2 To iTabNumber

                        Sheets(vArray(iTabStart)).Select False

                    Next iTabStart

                End If



                ActiveWindow.SelectedSheets.Copy



                ' *** the following code is optional, remove preceding apostrophes from the following four lines to enable password protection ***

                'For Each oSheet In ActiveWorkbook.Sheets

                    'oSheet.Protect "password"

                    'oSheet.EnableSelection = xlNoSelection

                'Next



                ActiveWorkbook.SaveAs FileName:= _

                "W:Financials" & iYear & "" & sDate & "Report to Distribute ElectronicallyDepartment Reports" _

                & sFolderName & "Current Year Financials" & "" & "Y" & ") " & iYear & "-" & sMonth & " Agent Report Card " & sVer & " - " & sManagerInitials & ".xls"

                ActiveWorkbook.Close

        End If



        iPercent = iManagerStart / iManagerNumber * 95

        Task_Progress (iPercent)

    Next iManagerStart

End With



Workbooks(sMasterListWBName).Close False



Task_Progress (100)

Unload frm_progress



Set oDistList = Nothing



With Application

    .EnableEvents = True

    .ScreenUpdating = True

End With



Message_Done



frm_agent.Show (vbModeless)



End Sub

I fixed it. I just added "Workbooks(sWbName).activate" at the end of the loop to make sure the focus is back on the raw data file. Now all files are saving in the correct format and location. Case closed unless someone has anything else to add. Maybe someone knows the reason the macro was losing sight of its active sheet (saving reference file instead of raw data file). Thank you.