Hi Experts,
i have come across an issue where in auto filter code is skipping over
without performing the required action and running next course of action.
I Have highlighted the line where am getting error can any one correct me
where am facing this issue.
Macro is executing well but this particular lines it is not performing the
action.
ActiveSheet.Range("A101:AY" & k).AutoFilter Field:=51, Criteria1:="<>"
& FileName
Rows("102:" & k).Select
Selection.Delete Shift:=xlUp
Range("A101").Select
Selection.AutoFilter
*complete code*
Public Sub OPEX_Reporting()
Dim Master As Workbook ' Macro and Master Workbook
'Dim Linked As Workbook ' CCA P&L Linked File
Dim NewFile As Workbook ' New File
Dim macro, CCA As Worksheet ' Worksheets in Macro and Master Workbook
'Dim Report, CON As Worksheet ' Worksheets in CCA P&L Linked File
Dim sh As Worksheet ' Searching for sheets
Dim rng As Range
Dim i, j, k, l As Double
Dim Outerloop, Innerloop As Integer
Dim PATH, PATH1 As String
Dim FileName As Double
Dim FileNamesave As Double
Set Master = ThisWorkbook
Set macro = Master.Sheets("Macro")
i = macro.Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
Application.DisplayAlerts = False
PATH = macro.Range("C4")
Dim Answer As String
Dim MyNote As String
'Place your text here
MyNote = "You are about to run the Macro. Are you Sure?" & Chr(13) &
Chr(13) & "If Yes! Make sure the below Path is empty - " & Chr(13) &
Chr(13) & PATH
'Display MessageBox
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Macro Confirmation
Message !!!")
If Answer = vbNo Then
'Code for No button Press
'MsgBox "You pressed NO!"
End
Else
'Code for Yes button Press
'MsgBox "You pressed Yes!"
End If
For Outerloop = 8 To 10
j = macro.Cells(Outerloop, Columns.Count).End(xlToLeft).Column
FileName = macro.Cells(Outerloop, 1)
Set NewFile = Workbooks.Add
For Innerloop = 3 To j
Dim CC As String
CC = ""
CC = macro.Cells(Outerloop, Innerloop)
Set sh = Master.Worksheets(CC)
sh.Activate
ActiveSheet.Copy before:=NewFile.Sheets(1)
k = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
NewFile.Sheets(CC).Select
Cells.Find(What:="Mapping Data", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False, SearchFormat:=False).Activate
' If Sheets(CC).FilterMode = True Then
' Selection.AutoFilter
' Else
' Selection.AutoFilter
' End If
If CC = "Transaction Details" Then
ActiveSheet.Range("A101:AY" & k).AutoFilter Field:=51, Criteria1:="<>"
& FileName
Rows("102:" & k).Select
Selection.Delete Shift:=xlUp
Range("A101").Select
Selection.AutoFilter
ElseIf CC = "Phased Actuals" Then
ActiveSheet.Range("A49:AY" & k).AutoFilter Field:=51, Criteria1:="<>" &
FileName
Rows("50:" & k).Select
Selection.Delete Shift:=xlUp
Range("A49").Select
Selection.AutoFilter
ElseIf CC = "Summary" Then
Rows("11:11").Select
ActiveSheet.Range("$A$11:$BF$19" & k).AutoFilter Field:=50,
Criteria1:="<>" & FileName
Rows("12:" & k).Select
Selection.Delete Shift:=xlUp
Range("a11").Select
Selection.AutoFilter
'ElseIf CC = "Nat Exp Vs Plan" Then
'ActiveSheet.Range("A94:AH" & k).AutoFilter Field:=35, Criteria1:="<>"
& FileName
'Rows("95:" & k).Select
'Selection.Delete Shift:=xlUp
'Range("A94").Select
'Selection.AutoFilter
'Else
'ActiveSheet.Range("A94:AH" & k).AutoFilter Field:=34, Criteria1:="<>"
& FileName
'Rows("95:" & k).Select
'Selection.Delete Shift:=xlUp
'Range("A94").Select
'Selection.AutoFilter
End If
Next
NewFile.Activate
On Error Resume Next
NewFile.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Delete
NewFile.SaveAs FileName:=PATH & "\" & FileName,
FileFormat:=xlOpenXMLWorkbook
NewFile.Close
Next
macro.Activate
MsgBox "You have run the Macro Successfully!!!"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Thanks & Regards,
Ram
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
