You have highlighted 5 lines
Try stepping through the code and finding out which line does not work as
required.
Regards
David Grugeon
On 28 April 2013 16:25, <knowledgeforex...@gmail.com> wrote:
> 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.
>
>
>
--
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.
