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

G

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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Rich (BB code): 
Sub NewFilter()

End Sub
Dim thisWB  As String

Remove the line in red. :)
 
Upvote 0
Ooppss.. Still Getting Compiling Error with Invalid Qualifier at below code line:

Code: 
Set ws1 = thisWB.Sheets("Collation1") 'this contains the text file
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
As far as I can see the code doesn't replace any file, it creates a new file.
 
Upvote 0
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.
 
Upvote 0
:):)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
:):)
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
Error 1004 on VlookUp
Replies
5
Views
234
Anthony47
A
H
  • Question
Macro to open up files in sub folder that have the lastest modified date as well as those 60 days before that date
Replies
1
Views
464
howard
H
S
  • Question
Excel VBA Getting error when creating pivot chart but error only occurs after sub that creates pivot tables. No error if directly called
Replies
0
Views
613
Steve48
S
jalrs
  • Solved
Loop Reading Wrong Value
2
Replies
11
Views
781
jalrs
jalrs
winstela
  • Solved
Sub or Function not defined
2
Replies
10
Views
869
Micron
M

Forum statistics

Threads
1,214,385
Messages
6,119,210
Members
448,874
Latest member
b1step2far

Share this page

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
Back
Top