Getting automation error but not sure why!!

[natekris8183]

The following code is taking some data for a statistic "form" (it's just the pretty user friendly version of the datat entry side) and moving it over to a raw data table to be connected with a pivot table. The macro is launched through an form control on the page. It seems to foul at the resizing of the table back to end at $AC$7800 (this is just an erroneous number as it will leave room for a decades worth of entries). Any thoughts?

Sub CopyPasteDataSheet()
    Dim DeliveryDate, OrderLimit, Division, BranchName, Hundreds, Fifties, Twenties, Tens, _
    Fives, Twos, Ones, Dollars, Halves, Quarters, Dimes, Nickels, Pennies, CoinDep, CurrDep As Range
    Dim Branch As String
    
    Set DeliveryDate = Range("D6")
    Set OrderLimit = Range("D5")
    Set Division = Range("D4")
    Set BranchName = Range("C3")
    Set Hundreds = Range("F9")
    Set Fifties = Range("F10")
    Set Twenties = Range("F11")
    Set Tens = Range("F12")
    Set Fives = Range("F13")
    Set Twos = Range("F14")
    Set Ones = Range("F15")
    Set Dollars = Range("F19")
    Set Halves = Range("F20")
    Set Quarters = Range("F21")
    Set Dimes = Range("F22")
    Set Nickels = Range("F23")
    Set Pennies = Range("F24")
    Set CoinDep = Range("E30")
    Set CurrDep = Range("E31")
    Branch = ActiveSheet.Range("C3")
    
    Sheets(Branch).Unprotect
    Sheets(Branch).Range("D521").End(xlUp).Offset(1, 0).FormulaR1C1 = DeliveryDate
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 1).FormulaR1C1 = BranchName
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 2).FormulaR1C1 = Hundreds
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 3).FormulaR1C1 = Fifties
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 4).FormulaR1C1 = Twenties
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 5).FormulaR1C1 = Tens
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 6).FormulaR1C1 = Fives
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 7).FormulaR1C1 = Twos
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 8).FormulaR1C1 = Ones
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 10).FormulaR1C1 = Dollars
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 11).FormulaR1C1 = Halves
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 12).FormulaR1C1 = Quarters
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 13).FormulaR1C1 = Dimes
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 14).FormulaR1C1 = Nickels
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 15).FormulaR1C1 = Pennies
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 18).FormulaR1C1 = OrderLimit
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 19).FormulaR1C1 = BranchName
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 20).FormulaR1C1 = Division
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 21).FormulaR1C1 = Division
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 24).FormulaR1C1 = CoinDep
    Sheets(Branch).Range("D521").End(xlUp).Offset(0, 25).FormulaR1C1 = CurrDep
    Sheets(Branch).Range("tblMiamiSt[#All]").RemoveDuplicates Columns:=Array(1, 2 _
        , 10, 17, 18), Header:=xlYes
    Sheets(Branch).ListObjects("tblMiamiSt").Resize Range("$D$3:$AC$7800")
    Sheets(Branch).Protect
End Sub

 

[sijpie]

Are you changing the number of columns in your resize? You can't do that.