Hello,
I am building a Macro to filter data to another workbook for with small steps and checking for any error. and after below code, I found Compile Error. Kindly suggest the correction.
Here in the code I have defined Main workbook name and its worksheets. Then also defined filter value. Then checked the existing file on desktop, if available need to delete and then created new workbook with the same name at same path.
I am building a Macro to filter data to another workbook for with small steps and checking for any error. and after below code, I found Compile Error. Kindly suggest the correction.
Here in the code I have defined Main workbook name and its worksheets. Then also defined filter value. Then checked the existing file on desktop, if available need to delete and then created new workbook with the same name at same path.
Code:
Sub NewFilter()
End Sub
Dim thisWB As String
Dim sPath As String
Dim sFile As String
Dim newWB1 As String
Dim FSO
Dim ws1, ws2, ws3, ws4 As Worksheet
Dim filter1, filter2, filter3 As String
Dim lrow As Double
thisWB = ActiveWorkbook.Name
sFile = ActiveWorkbook.Sheets("Main").Range("P1").Value
Set ws1 = thisWB.Sheets("Collation1") 'this contains the text file
Set ws2 = thisWB.Sheets("Main") 'this contains the filters
Set ws3 = thisWB.Sheets("Collation2") ' this is the destination
Set ws3 = thisWB.Sheets("Collation3") ' this is the destination
With ws2
filter1 = "=" & .Range("C5").Value
filter2 = "=" & .Range("C6").Value
End With
Set FSO = CreateObject("Scripting.FileSystemObject")
'Check File Exists or Not
If FSO.FileExists(sFile) Then
'If file exists, It will delete the file from source location
FSO.DeleteFile sFile, True
End If
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=sFile
newWB1 = ActiveWorkbook.Name
End Sub