Running and Step into gives different results.

agas

New Member
Joined
Mar 22, 2011
Messages
41
I cant run this macro. I can only makw it work with step into and with F8.

I have no idea why.

Code:
Sub MACRONUNKRALI()
If ActiveWorkbook.Name = "TK_LO_Cap_Rep.xls" Then
    Dim wb As Workbook
    For Each wb In Application.Workbooks
    
    wb.Activate
    If ActiveWorkbook.Name = "TK_LO_Cap_Rep.xls" Then
    
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("A:A").EntireColumn.AutoFit
    Columns("N:N").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").ColumnWidth = 8.17
    Columns("C:C").EntireColumn.AutoFit
    Rows("2:2").Select
    Selection.AutoFilter
    ActiveSheet.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).AutoFilter Field:=4, Criteria1:="Total"
    Range("A4").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        
    End With
    Range("E4").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    ActiveSheet.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).AutoFilter Field:=4
    
    Else
     
    If ActiveWorkbook.Name = "5519_capacity_report_short.xls" Then
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Range("L6").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").EntireColumn.AutoFit
    Columns("F:F").EntireColumn.AutoFit
    Columns("G:G").EntireColumn.AutoFit
    Columns("H:H").EntireColumn.AutoFit
    Columns("I:I").EntireColumn.AutoFit
    Columns("J:J").EntireColumn.AutoFit
    Columns("K:K").EntireColumn.AutoFit
    Columns("L:L").EntireColumn.AutoFit
    Columns("M:M").EntireColumn.AutoFit
'   reordered
    
        Rows("2:2").Select
    Selection.AutoFilter
    
'   Added autofilter
    With ActiveSheet
        .Range(Selection, ActiveCell.SpecialCells(xlLastCell)).AutoFilter Field:=15, _
            Criteria1:="00 - Capacity", _
            Operator:=xlFilterValues
        .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .Range("$A$2:$AA$17").AutoFilter Field:=15
    End With
'   capacity deleted
    
    With Workbooks("TK_LO_CAP_REP.xls")
    ActiveSheet.Move After:=.Worksheets(.Worksheets.Count)
    End With
    
    
'   moved to TK_LO_CAP_REP
'----------------------------------------------
   Else
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Range("L6").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").EntireColumn.AutoFit
    Columns("F:F").EntireColumn.AutoFit
    Columns("G:G").EntireColumn.AutoFit
    Columns("H:H").EntireColumn.AutoFit
    Columns("I:I").EntireColumn.AutoFit
    Columns("J:J").EntireColumn.AutoFit
    Columns("K:K").EntireColumn.AutoFit
    Columns("L:L").EntireColumn.AutoFit
    Columns("M:M").EntireColumn.AutoFit
'   reordered
    
        Rows("2:2").Select
    Selection.AutoFilter
    
'   Added autofilter
    With ActiveSheet
        .Range(Selection, ActiveCell.SpecialCells(xlLastCell)).AutoFilter Field:=15, _
            Criteria1:="00 - Capacity", _
            Operator:=xlFilterValues
        .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .Range("$A$2:$AA$17").AutoFilter Field:=15
    End With
   
'   capacity deleted
    With Workbooks("TK_LO_CAP_REP.xls")
    ActiveSheet.Move After:=.Worksheets(.Worksheets.Count)
    End With
    End If
    End If
    
  Next wb
Else
       
        
        ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\" & "TK_LO_CAP_REP" _
            , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
     
        'AAP
        
        Dim ws As Worksheet
        Dim LstCol As Integer, y() As Variant
        
        ChDir "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\"
        Workbooks.Open Filename:= _
            "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\TK_LO_CAP_REP.xls"
        
        For Each ws In ActiveWorkbook.Worksheets
            With ws
                .Select
                .Range(Selection, ActiveCell.SpecialCells(xlLastCell)).AutoFilter Field:=1, Criteria1:=Array( _
                    "AYT", "DGT", "DHA", "DHQ", "DLB", "DLD", "DLI", "ETF", "FAA", "GBB", "IMT", "TIE", "TIP", _
                    "YSE", "YSM", "YSP", "MAA", "YSA"), Operator:=xlFilterValues
                .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                .Range("$A$3:$AG$10000").AutoFilter Field:=1
            End With
                
                If ws.Name <> "TK_LO_CAP_REP" And ws.Name <> "5519_capacity_report_short" Then
                    With ws
        '                Sub Subtotal()
                        
                        LstCol = [iv1].End(xlToLeft).Column
                        y = Application.Transpose(Evaluate("row(13:" & LstCol & ")"))
                        Range("a1").End(xlDown).End(xlToRight).Subtotal GroupBy:=5, _
                        Function:=xlSum, TotalList:=y, Replace:=True, _
                        PageBreaks:=False, SummaryBelowData:=True
                        
                        
                     End With
                End If
        Next ws
        
        ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\" & "AAP_TK_LO_CAP_REP  (" & Format(Date, "dd-mm-yyyy") & ").xls" _
            , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
        
        'AYT
        
        ChDir "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\"
        Workbooks.Open Filename:= _
            "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\TK_LO_CAP_REP.xls"
        
        For Each ws In ActiveWorkbook.Worksheets
            With ws
                .Select
                .Range(Selection, ActiveCell.SpecialCells(xlLastCell)).AutoFilter Field:=1, Criteria1:=Array( _
                "AAP", "DGT", "DHA", "DHQ", "DLB", "DLD", "DLI", "ETF", "FAA", "GBB", "IMT", "TIE", "TIP", _
                "YSE", "YSM", "YSP", "MAA", "YSA"), Operator:=xlFilterValues
                .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                .Range("$A$3:$AG$10000").AutoFilter Field:=1
            End With
                
                If ws.Name <> "TK_LO_CAP_REP" And ws.Name <> "5519_capacity_report_short" Then
                    With ws
        '                Sub Subtotal()
                         
                        LstCol = [iv1].End(xlToLeft).Column
                        y = Application.Transpose(Evaluate("row(13:" & LstCol & ")"))
                        Range("a1").End(xlDown).End(xlToRight).Subtotal GroupBy:=5, _
                        Function:=xlSum, TotalList:=y, Replace:=True, _
                        PageBreaks:=False, SummaryBelowData:=True
                        
                        
                     End With
                End If
        Next ws
        
        ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\" & "AYT_TK_LO_CAP_REP  (" & Format(Date, "dd-mm-yyyy") & ").xls" _
            , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
        
        'DELTA
        
        ChDir "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\"
        Workbooks.Open Filename:= _
            "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\TK_LO_CAP_REP.xls "
        
        For Each ws In ActiveWorkbook.Worksheets
            With ws
                .Select
                .Range(Selection, ActiveCell.SpecialCells(xlLastCell)).AutoFilter Field:=1, Criteria1:=Array( _
                "AAP", "AYT", "DHA", "DHQ", "ETF", "FAA", "GBB", "IMT", "TIE", "TIP", _
                "YSE", "YSM", "YSP", "MAA", "YSA"), Operator:=xlFilterValues
                .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                .Range("$A$3:$AG$10000").AutoFilter Field:=1
            End With
                
                If ws.Name <> "TK_LO_CAP_REP" And ws.Name <> "5519_capacity_report_short" Then
                    With ws
        '                Sub Subtotal()
                         
                        LstCol = [iv1].End(xlToLeft).Column
                        y = Application.Transpose(Evaluate("row(13:" & LstCol & ")"))
                        Range("a1").End(xlDown).End(xlToRight).Subtotal GroupBy:=5, _
                        Function:=xlSum, TotalList:=y, Replace:=True, _
                        PageBreaks:=False, SummaryBelowData:=True
                        
                        
                     End With
                End If
        Next ws
        
        ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\" & "DELTA_TK_LO_CAP_REP  (" & Format(Date, "dd-mm-yyyy") & ").xls" _
            , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
        
        'ETF
        
        ChDir "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\"
        Workbooks.Open Filename:= _
            "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\TK_LO_CAP_REP.xls "
        
        For Each ws In ActiveWorkbook.Worksheets
            With ws
                .Select
                .Range(Selection, ActiveCell.SpecialCells(xlLastCell)).AutoFilter Field:=1, Criteria1:=Array( _
                "AAP", "DGT", "DHA", "DHQ", "DLB", "DLD", "DLI", "AYT", "FAA", "GBB", "IMT", "TIE", "TIP", _
                "YSE", "YSM", "YSP", "MAA", "YSA"), Operator:=xlFilterValues
                .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                .Range("$A$3:$AG$10000").AutoFilter Field:=1
            End With
                
                If ws.Name <> "TK_LO_CAP_REP" And ws.Name <> "5519_capacity_report_short" Then
                    With ws
        '                Sub Subtotal()
                         
                        LstCol = [iv1].End(xlToLeft).Column
                        y = Application.Transpose(Evaluate("row(13:" & LstCol & ")"))
                        Range("a1").End(xlDown).End(xlToRight).Subtotal GroupBy:=5, _
                        Function:=xlSum, TotalList:=y, Replace:=True, _
                        PageBreaks:=False, SummaryBelowData:=True
                        
                        
                     End With
                End If
        Next ws
        
        ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\" & "ETF_TK_LO_CAP_REP  (" & Format(Date, "dd-mm-yyyy") & ").xls" _
            , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
        
        'GBB
        
        ChDir "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\"
        Workbooks.Open Filename:= _
            "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\TK_LO_CAP_REP.xls "
        
        For Each ws In ActiveWorkbook.Worksheets
            With ws
                .Select
                .Range(Selection, ActiveCell.SpecialCells(xlLastCell)).AutoFilter Field:=1, Criteria1:=Array( _
                "AAP", "DGT", "DHA", "DHQ", "DLB", "DLD", "DLI", "ETF", "FAA", "AYT", "IMT", "TIE", "TIP", _
                "YSE", "YSM", "YSP", "MAA", "YSA"), Operator:=xlFilterValues
                .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                .Range("$A$3:$AG$10000").AutoFilter Field:=1
            End With
                
                If ws.Name <> "TK_LO_CAP_REP" And ws.Name <> "5519_capacity_report_short" Then
                    With ws
        '                Sub Subtotal()
                         
                        LstCol = [iv1].End(xlToLeft).Column
                        y = Application.Transpose(Evaluate("row(13:" & LstCol & ")"))
                        Range("a1").End(xlDown).End(xlToRight).Subtotal GroupBy:=5, _
                        Function:=xlSum, TotalList:=y, Replace:=True, _
                        PageBreaks:=False, SummaryBelowData:=True
                        
                        
                     End With
                End If
        Next ws
        
        ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\" & "GBB_TK_LO_CAP_REP  (" & Format(Date, "dd-mm-yyyy") & ").xls" _
            , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
        
        'IMT
        
        ChDir "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\"
        Workbooks.Open Filename:= _
            "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\TK_LO_CAP_REP.xls "
        
        For Each ws In ActiveWorkbook.Worksheets
            With ws
                .Select
                .Range(Selection, ActiveCell.SpecialCells(xlLastCell)).AutoFilter Field:=1, Criteria1:=Array( _
                "AAP", "DGT", "DHA", "DHQ", "DLB", "DLD", "DLI", "ETF", "FAA", "GBB", "AYT", "TIE", "TIP", _
                "YSE", "YSM", "YSP", "MAA", "YSA"), Operator:=xlFilterValues
                .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                .Range("$A$3:$AG$10000").AutoFilter Field:=1
            End With
                
                If ws.Name <> "TK_LO_CAP_REP" And ws.Name <> "5519_capacity_report_short" Then
                    With ws
        '                Sub Subtotal()
                         
                        LstCol = [iv1].End(xlToLeft).Column
                        y = Application.Transpose(Evaluate("row(13:" & LstCol & ")"))
                        Range("a1").End(xlDown).End(xlToRight).Subtotal GroupBy:=5, _
                        Function:=xlSum, TotalList:=y, Replace:=True, _
                        PageBreaks:=False, SummaryBelowData:=True
                        
                        
                     End With
                End If
        Next ws
        
        ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\" & "IMT_TK_LO_CAP_REP  (" & Format(Date, "dd-mm-yyyy") & ").xls" _
            , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
                
        'TIETIP
        
        ChDir "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\"
        Workbooks.Open Filename:= _
            "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\TK_LO_CAP_REP.xls "
        
        For Each ws In ActiveWorkbook.Worksheets
            With ws
                .Select
                .Range(Selection, ActiveCell.SpecialCells(xlLastCell)).AutoFilter Field:=1, Criteria1:=Array( _
                "AAP", "DGT", "DHA", "DHQ", "DLB", "DLD", "DLI", "ETF", "FAA", "GBB", "IMT", "AYT", _
                "YSE", "YSM", "YSP", "MAA", "YSA"), Operator:=xlFilterValues
                .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                .Range("$A$3:$AG$10000").AutoFilter Field:=1
            End With
                
                If ws.Name <> "TK_LO_CAP_REP" And ws.Name <> "5519_capacity_report_short" Then
                    With ws
        '                Sub Subtotal()
                         
                        LstCol = [iv1].End(xlToLeft).Column
                        y = Application.Transpose(Evaluate("row(13:" & LstCol & ")"))
                        Range("a1").End(xlDown).End(xlToRight).Subtotal GroupBy:=5, _
                        Function:=xlSum, TotalList:=y, Replace:=True, _
                        PageBreaks:=False, SummaryBelowData:=True
                        
                        
                     End With
                End If
        Next ws
        
        ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\" & "TIETIP_TK_LO_CAP_REP  (" & Format(Date, "dd-mm-yyyy") & ").xls" _
            , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
        
        'YESIM
        
        ChDir "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\"
        Workbooks.Open Filename:= _
            "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\TK_LO_CAP_REP.xls "
        
        For Each ws In ActiveWorkbook.Worksheets
            With ws
                .Select
                .Range(Selection, ActiveCell.SpecialCells(xlLastCell)).AutoFilter Field:=1, Criteria1:=Array( _
                "AAP", "DGT", "DHA", "DHQ", "DLB", "DLD", "DLI", "ETF", "FAA", "GBB", "IMT", "TIE", "TIP", _
                "AYT", "MAA"), Operator:=xlFilterValues
                .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                .Range("$A$3:$AG$10000").AutoFilter Field:=1
            End With
                
                If ws.Name <> "TK_LO_CAP_REP" And ws.Name <> "5519_capacity_report_short" Then
                    With ws
        '                Sub Subtotal()
                         
                        LstCol = [iv1].End(xlToLeft).Column
                        y = Application.Transpose(Evaluate("row(13:" & LstCol & ")"))
                        Range("a1").End(xlDown).End(xlToRight).Subtotal GroupBy:=5, _
                        Function:=xlSum, TotalList:=y, Replace:=True, _
                        PageBreaks:=False, SummaryBelowData:=True
                        
                        
                     End With
                End If
        Next ws
        
        ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\cap rep\1\" & "Yesim_TK_LO_CAP_REP  (" & Format(Date, "dd-mm-yyyy") & ").xls" _
            , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
        
        End If
End Sub
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
When you run it, what line does it stop on? Or what error message do you get? Your code below is too long to work through to identify this.
 
Upvote 0
It stops at "Selection.AutoFilter" line. And gives
Runtime Error '1004':

The commandcouldnt not be completed by using the range specified.
Select a single cell within the range and try the command again.
 
Upvote 0
It means that you have nothing to filter.
 
Upvote 0
You selected second row and remove filter. Perphaps, there's no autofilter or there's no data to remove filter from.
 
Upvote 0
There isnt because it skips the first part of the code.

It skips the first line "If ActiveWorkbook.Name = "TK_LO_Cap_Rep.xls" Then"
so there is no filtered data.
But if i do this with step into. It works perfect.
 
Upvote 0

Forum statistics

Threads
1,224,548
Messages
6,179,448
Members
452,915
Latest member
hannnahheileen

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