Removing a workbook variable in a loop

Question

I assigned the book to a variable. Then I do some things and save the file and close the book:

Is setting up the Nothing workbook a good idea if I want to use this code in a loop?

    For i = 1 To UBound(a_ven_lst1)
        Set wb_input1 = Application.Workbooks.Add
        Set ws_input1 = wb_input1.Sheets(1)
        .Rows(1).Copy ws_input1.Rows(1)
        .Rows(d_fst_ven_row & ":" & d_lst_ven_row).Copy ws_input1.Rows(2)
        s_save_path = f_str_file_name(ws_input1.Cells(2, i_ven_col_cnt).Value)
        s_file_path = s_path & "\" & s_save_path & ".xlsx"
        a_ven_lst1(i, 2) = s_file_path
        wb_input1.SaveAs Filename:=s_file_path
        wb_input1.Close True
        Set wb_input1 = Nothing
    Next i

Am I killing this variable appropriately? I saw some objects that still exist in the VBA editor after crushing the code, but far beyond this point.

Answer 1

alternative code so you don’t have to worry about setting the variable to a working variable and removing it

With ws_input

    '...

    Dim rowToCopy1 As Range, rowToCopy2 As Range        
    Set rowToCopy1 = .Rows(1) '<--| set first row to copy, since it "constant" against the subsequent loop
    Set rowToCopy2 = .Rows(d_fst_ven_row & ":" & d_lst_ven_row) '<--| set second row to copy, since it "constant" against the subsequent loop

    For i = 1 To UBound(a_ven_lst1)
        With Application.Workbooks.Add '<--| open a new workbook and reference its instance
            With .Sheets(1) '<--| reference referenced workbook sheet(1)
                rowToCopy1.Copy .Rows(1)
                rowToCopy1.Copy .Rows(2)
                s_save_path = f_str_file_name(.Cells(2, i_ven_col_cnt).Value)
            End With
            s_file_path = s_path & "\" & s_save_path & ".xlsx"
            a_ven_lst1(i, 2) = s_file_path
            .SaveAs Filename:=s_file_path
            .Close True
        End With '<--| discard the instance of the opened workbok
    Next i

    '...

End With