CODEWITHSUNDEEP
excelvbaautofill
Skwiggs
Skwiggs

Reputation: 1436

Excel VBA - Autofill only works during debug

I am trying to copy cells from a sheet in a table on another sheet, and trying to autofill the formulas contained in the table to the newly created rows.

My macro basically takes all visible cells from a sorted table in sheet "Demands" and copies their value directly under the table in the sheet "ITA & IO-EOTP". The table automatically expands, but not all formulas are applied to the new row (some are, though). I then call an AutoFill from the row above to the new row. When all new IDs are copied over, I sort everything.

The thing is: while the AutoFill works when I'm debugging line by line, it is simply skipped when I launch the macro without breakpoints... I don't know what is the cause of this ! I've already spent the whole day trying to figure it out, and it's driving me nuts !!

Here is the whole macro:

Public Sub InsertIntoITA()
Dim y, availPos, startPos As Long
Dim currCell, currSel As Range

'make it fast, plz
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

ThisWorkbook.Activate
Sheets("Demands").Select

demandsImported = True

'check if demands were imported first
If demandsImported Then

    'select all new demands
    y = Range("A65536").End(xlUp).Row
    Set currSel = Range("A2:A" & y).SpecialCells(xlCellTypeVisible)

    'get first position after the table in ITA sheet
    availPos = Sheets("ITA & IO-EOTP").Range("A65536").End(xlUp).Row + 1
    startPos = availPos - 1

    'check if there are demands at all
    If Not currSel.Count = 0 Then
        For Each currCell In currSel

            'if cell contains #N/A (not what we want) it means the user hasn't attributed a new ID to the demand yet
            'if it doesn't, proceed to copy the ID at the end of the table in ITA sheet
            If Not IsError(currCell) Then
                Sheets("ITA & IO-EOTP").Range("A" & availPos).Value = currCell.Value

                'then autofill from the line above, to ensure formulas are applied
                Range("B" & availPos - 1 & ":P" & availPos - 1).AutoFill Destination:=Range("B" & availPos - 1 & ":P" & availPos)

                'allow the table to automatically expand to include the new ID then
                'increment available position
                availPos = availPos + 1
            End If
        Next currCell

        'Then order them
        Sheets("ITA & IO-EOTP").ListObjects("ITATable").Sort. _
        SortFields.Clear
        Sheets("ITA & IO-EOTP").ListObjects("ITATable").Sort. _
        SortFields.Add Key:=Range("ITATable[[#All],[ONE-IT ID]]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With Sheets("ITA & IO-EOTP").ListObjects("ITATable").Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If
End If

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

I hope you'll be able to see where the problem is...

(Also quick note: the ITA Sheet has a macro that sets .EnableCalculation to False whenever it is activated, don't know if it matters/helps)

Private Sub Worksheet_Activate()
Sheets("ITA & IO-EOTP").EnableCalculation = False
End Sub

Upvotes: 0

Views: 1112

Answers (1)

Skwiggs
Skwiggs

Reputation: 1436

Okay, found the culprit... I just needed to activate the ITA sheet before the compiler enters the For block... 

        Sheets("ITA & IO-EOTP").Activate
        For Each currCell In currSel

            'if cell contains #N/A (not what we want) it means the user hasn't attributed a new ID to the demand yet
            'if it doesn't, proceed to copy the ID at the end of the table in ITA sheet
            If Not IsError(currCell) Then
                Sheets("ITA & IO-EOTP").Range("A" & availPos).Value = currCell.Value

                'then autofill from the line above, to ensure formulas are applied
                Range("B" & availPos - 1 & ":P" & availPos - 1).AutoFill Destination:=Range("B" & availPos - 1 & ":P" & availPos)

                'increment available position
                availPos = availPos + 1
            End If
        Next currCell

... Now it works with both autofill or copy/paste

Upvotes: 1

Related Questions