Getting automation error but not sure why!!


Board Regular
Mar 12, 2013
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).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")
End Sub

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Are you changing the number of columns in your resize? You can't do that.
Upvote 0

Forum statistics

Latest member

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
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 "".
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