No errors, but Macro works using F8 line by line, not when executing the full macro - excel, vba

Question

I have 6 identical macros in one workbook. 4 out of 6 work good, but I have the same issue for the rest.

If I run the macro from debug window with F8, I have perfect , expected results. If I run a macro normally, I have not any errors, but the result is obviously wrong. I can guess that at that case , that the macto ignores this part (all mistakes start here), but not sure

ActiveSheet.Range("H2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3],RC[-3])"
ActiveSheet.Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H" & lastrow)
ActiveSheet.Range("H2:H" & lastrow).Select

The goal of the macro is to filter one tab, put a few columns in another tab; compare values from one of the columns to another tab, remove duplicates , filter and paste the results in the "Results" tab. When I do this manually I have got 6 rows in a "Result" tab. When I run it normally, I have one row, or nothing..

Can you please kindly advise - what is wrong with this macro?

I have tried to put this line in my code (no luck) : Application.PrintCommunication = True

I have tried to put DoEvents ThisWorkbook before each Row, Column and Range - no luck

Many thanks in advance!!

And here is my full code:

Public lastrow As Long
Public FileName As String
Public TabName As String

Sub APP_filtering_new()
'
' APP_filtering Macro


lastrow = ActiveSheet.Range("A1048576").End(xlUp).Row

Sheets("APP-input").Select

    ActiveSheet.Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$AG$14878").AutoFilter Field:=2, Criteria1:=Array( _
        "BRAMPTON", "VANCOUVER, CD", "VANCOUVER", _
        "VANCOUVER TERMINAL"), Operator:=xlFilterValues
    ActiveSheet.Columns("E:E").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("APP_output").Select
    ActiveSheet.Columns("A:A").Select
    ActiveSheet.Paste
    Sheets("APP-input").Select
    ActiveSheet.Columns("N:N").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("APP_output").Select
    ActiveSheet.Columns("D:D").Select
    ActiveSheet.Paste
    Sheets("APP-input").Select
    ActiveSheet.Columns("G:G").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("APP_output").Select
    ActiveSheet.Columns("E:E").Select
    ActiveSheet.Paste
    ActiveSheet.Range("F2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = " "
    ActiveSheet.Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:F" & lastrow)
    ActiveSheet.Range("F2:F" & lastrow).Select
    ActiveSheet.Range("G2").Select
    ActiveCell.FormulaR1C1 = " "
    ActiveSheet.Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G" & lastrow)
    ActiveSheet.Range("G2:G" & lastrow).Select
    ActiveSheet.Range("H2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3],RC[-3])"
    ActiveSheet.Range("H2").Select
    Selection.AutoFill Destination:=Range("H2:H" & lastrow)
    ActiveSheet.Range("H2:H" & lastrow).Select
    ActiveSheet.Columns("H:H").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

       'remove duplicates

    ActiveSheet.Columns("A:H").Select
    Application.CutCopyMode = False
    ActiveSheet.Range("A1:E" & lastrow).RemoveDuplicates Columns:=5, Header:= _
        xlNo

        'vlookup, IF condition

    ActiveSheet.Range("I2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],container,4,FALSE)"
    ActiveSheet.Range("I2").Select
    Selection.AutoFill Destination:=Range("I2:I" & lastrow)
    ActiveSheet.Range("I2:I" & lastrow).Select
    ActiveSheet.Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]<RC[-2],""C. has bigger number of Containers"",IF(RC[-1]=RC[-2],""The same amount of containers"",IF(RC[-2]<RC[-1],""The C. has less amount of Containers"")))"
    ActiveSheet.Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J" & lastrow)
    ActiveSheet.Range("J2:J" & lastrow).Select
    ActiveSheet.Range("H1").Select
    ActiveCell.FormulaR1C1 = "Amt of Containers - External report"
    ActiveSheet.Range("I1").Select
    ActiveCell.FormulaR1C1 = "Amt of Containers - Internal report"
    ActiveSheet.Range("J1").Select
    ActiveCell.FormulaR1C1 = "Result (N/A means New Shipment)"
    ActiveSheet.Range("H1:J1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveSheet.Range("H1:I1").Select
    ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Range("J1").Select
    ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Range("J1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("D1:J" & lastrow).AutoFilter Field:=7, Criteria1:=Array( _
        "#N/A", "C. has bigger number of Containers", _
        "The C. has less amount of Containers"), Operator:=xlFilterValues

     ' paste in next empty row

    ActiveSheet.Rows("2:2").Select
    ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy

     Sheets("Results").Select
    lastrow = ActiveSheet.Range("A1048576").End(xlUp).Row

    ActiveSheet.Range("A" & lastrow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

     End Sub

Please read [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1) first. — SJR, Aug 30 '18 at 19:27
Thanks for a reply! I have read it mindfully. I am very new to vba, and don't really understand how I can apply this hints to my code, Have tried to replace ActiveSheet.Range("G2").Select with Workbook(Book1).Worksheets("APP_output").Range("G2"), but got a debug window at that place. And the biggest question that I have - why does the same code works on other spreadsheets, and ignores these two? — Irina, Aug 30 '18 at 20:24
When you use activesheet and select your code can end up operating on the wrong sheet or cell. Try stepping through your code to see what's going on. If you remove the extraneous Selects etc your code will be much cleaner and quicker and any remaining problems will be easier to spot. — SJR, Aug 31 '18 at 10:20

score 0 · Accepted Answer · answered Aug 31 '18 at 10:22

0

This isn't a full answer, but e.g this block of code

ActiveSheet.Range("H2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3],RC[-3])"
ActiveSheet.Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H" & lastrow)
ActiveSheet.Range("H2:H" & lastrow).Select

can be replaced by a single line

ActiveSheet.Range("H2:H" & lastrow).FormulaR1C1 = "=COUNTIF(C[-3],RC[-3])"

Get rid of ActiveSheet and replace with the actual sheet name.

answered Aug 31 '18 at 10:22

SJR

22,986
6
18
26

1

That gives me a great impulse; thanks a lot, my macros work now! – Irina Aug 31 '18 at 20:12

No errors, but Macro works using F8 line by line, not when executing the full macro - excel, vba

1 Answers1