How to save and close workbook open in VBA as passing input parameter

  • 10 January 2022
  • 1 reply

Badge +3

I got a vba script from online in which i am passing input arg(Excel Path: my current workbook name) from AA to perform some action. Now I want to save my opened workbook which i pass as arg after vba execution.


Steps i used

Open Macro-> run Macro with passing input arg->close Macro.


Now i want to save and close the workbook which opened during vba function.

I tried using save and save as function but no luck. below vba code. After i save the worked i need to perform other logic too


Sub parse_data()

Sub Test(Excel_Path As String)

'Update by Extendoffice 2018/3/2



  Dim xRCount As Long

  Dim Wb As Workbook



  Dim xSht As Worksheet


  Dim xNSht As Worksheet


  Dim I As Long


  Dim xTRrow As Integer


  Dim xCol As New Collection


  Dim xTitle As String


  Dim xSUpdate As Boolean

  Set Wb = Workbooks.Open(Excel_Path)


  Set xSht = ActiveSheet

  xSht.Range("A1:Q1").AutoFilter field:=13, Criteria1:=xlFilterLastMonth, Operator:=xlFilterDynamic

  On Error Resume Next


  xRCount = xSht.Cells(xSht.Rows.Count, 2).End(xlUp).Row


  xTitle = "A1:Q1"


  xTRrow = xSht.Range(xTitle).Cells(2).Row


  For I = 2 To xRCount


    Call xCol.Add(xSht.Cells(I, 2).Text, xSht.Cells(I, 2).Text)




  xSUpdate = Application.ScreenUpdating


  Application.ScreenUpdating = False


  For I = 1 To xCol.Count


    Call xSht.Range(xTitle).AutoFilter(2, CStr(xCol.Item(I)))


    Set xNSht = Nothing


    Set xNSht = Worksheets(CStr(xCol.Item(I)))


    If xNSht Is Nothing Then


      Set xNSht = Worksheets.Add(, Sheets(Sheets.Count))


      xNSht.Name = CStr(xCol.Item(I))




      xNSht.Move , Sheets(Sheets.Count)


    End If


    xSht.Range("A" & xTRrow & ":A" & xRCount).EntireRow.Copy xNSht.Range("A1")







  xSht.AutoFilterMode = False




  Application.ScreenUpdating = xSUpdate




End Sub




1 reply

Badge +3

@NagaSujitha Krishna​  - before End Sub line write as below





If this helps you please do Like & mark as "Best Answer" so it will be helpful for others as well. 


Best Regards,