Complie Error: Only Comments may appear after End Sub, End Function or End Property

Gaurangg

Board Regular
Joined
Aug 6, 2015
Messages
134
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.


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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,322
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Rich (BB code):
Sub NewFilter()

End Sub
Dim thisWB  As String

Remove the line in red. :)
 

Gaurangg

Board Regular
Joined
Aug 6, 2015
Messages
134
Ooppss.. Still Getting Compiling Error with Invalid Qualifier at below code line:

Code:
Set ws1 = thisWB.Sheets("Collation1") 'this contains the text file
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,353
Office Version
  1. 365
Platform
  1. Windows
thisWB is a string, try this.
Code:
Sub NewFilter()



Dim thisWB As Workbook
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


    Set thisWB = ActiveWorkbook
    sFile = thisWB.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
 

Gaurangg

Board Regular
Joined
Aug 6, 2015
Messages
134
Hi Norie, Thanks for the help. However it was kept intentionally as for my further code it will require. I have made some changes in the code and it works fine for the first time. but then it gives the error "Runtime error 9: Subscript out of range" Because First time code creates the workbook with a name given in a cell P1 (C:\Users\Gaurangg\Desktop\New Report) But when again running the error, it doesn't get deleted the same name file and recreate it again. Can you please help for that.

Error comes at below line:

sFile = ActiveWorkbook.Sheets("Main").Range("P1").Value
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,353
Office Version
  1. 365
Platform
  1. Windows
Change ActiveWorkbook to ThisWorkbook, then the code will be referring to the workbook the code is in, which I assume is the workbook the sheet 'Main' is in.
Code:
Sub NewFilter()
Dim thisWB As Workbook
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


    Set thisWB = ThisWorkbook

    sFile = thisWB.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
 

Gaurangg

Board Regular
Joined
Aug 6, 2015
Messages
134
Hello Norie,

I have made the suggested changes in my code and it works properly. Thank you so much. But the question is the code is replacing the existing file instead of first deleting and then creating. Coz if it delete the file first then the option to replace will not come naa? Below is my working changed code, Please help.

Code:
Sub NewFilter()

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 = ThisWorkbook.Name
    sFile = ThisWorkbook.Sheets("Main").Range("P1").Value
    

Set ws1 = ThisWorkbook.Sheets("Raw") 'this contains the text file
Set ws2 = ThisWorkbook.Sheets("Main") 'this contains the filters
Set ws3 = ThisWorkbook.Sheets("Raw1") ' this is the destination
Set ws3 = ThisWorkbook.Sheets("Raw2") ' 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
    ActiveWorkbook.Close
    
    
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,353
Office Version
  1. 365
Platform
  1. Windows
As far as I can see the code doesn't replace any file, it creates a new file.
 

Gaurangg

Board Regular
Joined
Aug 6, 2015
Messages
134
practically it opens the new file and saving it at its destination with the same name and hence asked to replace the file. I want to delete the old file from the location first if any file available with same name and then create a new file and save it.
 

Gaurangg

Board Regular
Joined
Aug 6, 2015
Messages
134
:):)Hi All,

I have made some necessary changes in the code and got what I need. Lots of thanks for the friends who helped me Below is the Code for the reference which might help others too...

Code:
Sub NewFilter()

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 = ThisWorkbook.Name
    sFile = ThisWorkbook.Sheets("Main").Range("P1").Value
    

Set ws1 = ThisWorkbook.Sheets("Raw") 'this contains the text file
Set ws2 = ThisWorkbook.Sheets("Main") 'this contains the filters
Set ws3 = ThisWorkbook.Sheets("Raw1") 'this contains the text file
Set ws3 = ThisWorkbook.Sheets("Raw2") 'this contains the text file

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
    
    
'Filtering and copy pasting First Sheet Data.
    Windows(thisWB).Activate

    Sheets("Raw").Select
    Cells.Select
                
If ActiveSheet.AutoFilterMode = False Then
    Selection.AutoFilter
End If

    Selection.AutoFilter Field:=36, Criteria1:=filter1
    Selection.AutoFilter Field:=29, Criteria1:=filter2
                
    lastrow = Cells(Rows.Count, 39).End(xlUp).Row

    Rows("1:" & lastrow).Copy
    
    Windows(newWB1).Activate
    ActiveSheet.Paste
    ActiveSheet.Name = "IQ Collation"
    ActiveWorkbook.Save
    Range("A2").Select
    
    
    Windows(thisWB).Activate

    Sheets("Raw").Select

    Selection.AutoFilter Field:=36
    Selection.AutoFilter Field:=29
    Range("A2").Select
    
'Filtering and copy pasting Second Sheet Data.
    Sheets("Raw1").Select
    Cells.Select
                
If ActiveSheet.AutoFilterMode = False Then
    Selection.AutoFilter
End If

    Selection.AutoFilter Field:=36, Criteria1:=filter1
    Selection.AutoFilter Field:=29, Criteria1:=filter2
                
    lastrow = Cells(Rows.Count, 39).End(xlUp).Row

    Rows("1:" & lastrow).Copy
    
    Windows(newWB1).Activate
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    ActiveSheet.Name = "Error Collation"
    ActiveWorkbook.Save
    Range("A2").Select
    
    
    Windows(thisWB).Activate

    Sheets("Raw1").Select

    Selection.AutoFilter Field:=36
    Selection.AutoFilter Field:=29
    Range("A2").Select
    
'Filtering and copy pasting third Sheet Data.
    Sheets("Raw2").Select
    Cells.Select
                
If ActiveSheet.AutoFilterMode = False Then
    Selection.AutoFilter
End If

    Selection.AutoFilter Field:=36, Criteria1:=filter1
    Selection.AutoFilter Field:=29, Criteria1:=filter2
                
    lastrow = Cells(Rows.Count, 39).End(xlUp).Row

    Rows("1:" & lastrow).Copy
    
    Windows(newWB1).Activate
    Sheets("Sheet3").Select
    ActiveSheet.Paste
    ActiveSheet.Name = "Feedback Collation"
    Range("A2").Select
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    
    Windows(thisWB).Activate

    Sheets("Raw2").Select

    Selection.AutoFilter Field:=36
    Selection.AutoFilter Field:=29
    Range("A2").Select
    
    Sheets("Main").Select
    
    
End Sub
:):)
 

Forum statistics

Threads
1,175,753
Messages
5,899,292
Members
434,759
Latest member
carcharoth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top