ajc5382

New Member
Joined
Jun 25, 2012
Messages
41
Hi,

I have the following code that is used to count a variable number or rows of raw data, then extend formulas to the last raw data row in columns M to Q in my spreadsheet.

The error(User/Object defined error) occurs below in the line Selection.AutoFill Destination....etc

Thanks for your help!

Code:
Private Sub ExtendFormulas()
   Application.Calculation = xlCalculationManual
     Dim LastRow1 As Long
     LastRow1 = (Range("A" & Rows.Count).End(xlUp).Row) - 1
          Range("M2:Q2").Select
            Selection.AutoFill Destination:=Range("M3" & ":Q" & LastRow1)
   Application.Calculation = xlCalculationAutomatic
End Sub
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,383
Try this.

Code:
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] ExtendFormulas()
   Application.Calculation = xlCalculationManual
     [color=darkblue]Dim[/color] LastRow1 [color=darkblue]As[/color] [color=darkblue]Long[/color]
     LastRow1 = Range("A" & Rows.Count).End(xlUp).Row - 1
          Range("M2:Q2").AutoFill Destination:=Range("M2" & ":Q" & LastRow1)
   Application.Calculation = xlCalculationAutomatic
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

The destination should include the source cells M2:Q2
 

ajc5382

New Member
Joined
Jun 25, 2012
Messages
41
Try this.

Code:
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] ExtendFormulas()
   Application.Calculation = xlCalculationManual
     [color=darkblue]Dim[/color] LastRow1 [color=darkblue]As[/color] [color=darkblue]Long[/color]
     LastRow1 = Range("A" & Rows.Count).End(xlUp).Row - 1
          Range("M2:Q2").AutoFill Destination:=Range("M2" & ":Q" & LastRow1)
   Application.Calculation = xlCalculationAutomatic
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

The destination should include the source cells M2:Q2

Worked great! My issue now is that when I add my raw data(cols A-L), my macros automatically run. Do you know how to make it so they only run when I push my buttons? Thanks for your help.
 
Last edited:

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,383
Worked great! My issue now is that when I cut in past my raw data, my macros automatically run. Do you know how to make it so they only run when I push my buttons? Thanks for your help.

You're welcome.

The followup question is too vague for me to answer. How are you cutting and pasting? Do you have any event procedures like a Private Sub Worksheet_SelectionChange(ByVal Target As Range) ?
 

ajc5382

New Member
Joined
Jun 25, 2012
Messages
41

ADVERTISEMENT

I download raw data monthly, which varies only in rows of data and paste into columns A to L. Columns M2 > Q(depends on how many rows of raw data) have formulas that use the raw data to form column specific information.

No event procedures...I just have a simple sub to clear my formula cells(L2:Q(depends on rows of raw data), and then one that extends formulas down to the last row of data.

I'd like to be able to paste my data in, and only have it calculate if I push my button. Right now, I paste my data in and it begins calculating automatically.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,383
I download raw data monthly, which varies only in rows of data and paste into columns A to L. Columns M2 > Q(depends on how many rows of raw data) have formulas that use the raw data to form column specific information.

No event procedures...I just have a simple sub to clear my formula cells(L2:Q(depends on rows of raw data), and then one that extends formulas down to the last row of data.

I'd like to be able to paste my data in, and only have it calculate if I push my button. Right now, I paste my data in and it begins calculating automatically.

You clear all the formulas before you paste?

Show your Sub that clears the formulas.

The Sub ExtendFormulas uses existing formulas in M2:Q2. So those are not cleared.

You can set formula calculations to Manual in the Tools\Options menu. Then use the F9 keyboard key to trigger a calculation.
 

ajc5382

New Member
Joined
Jun 25, 2012
Messages
41
You clear all the formulas before you paste?

Show your Sub that clears the formulas.

The Sub ExtendFormulas uses existing formulas in M2:Q2. So those are not cleared.

You can set formula calculations to Manual in the Tools\Options menu. Then use the F9 keyboard key to trigger a calculation.


Code:
Sub ClearContents()
    Dim LastRow As Long
    LastRow = (Range("A" & Rows.Count).End(xlUp).Row) - 1
    Range("M3:" & "Q" & LastRow).ClearContents
End Sub

I have a button calling this sub, then after its clears, calls a sub to extend my formulas. Also - not sure why this is happening, but whenever I click my buttons they either keep getting bigger or the text in them keeps getting small.

Thanks for helping!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,850
Messages
5,598,457
Members
414,239
Latest member
xnanx

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
Top