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)
Next
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))
Else
xNSht.Move , Sheets(Sheets.Count)
End If
xSht.Range("A" & xTRrow & ":A" & xRCount).EntireRow.Copy xNSht.Range("A1")
xNSht.Columns.AutoFit
Next
xSht.AutoFilterMode = False
xSht.Activate
Application.ScreenUpdating = xSUpdate
End Sub
