Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

When you are pasting a formula manually in Excel, you can double click

on the bottom right hand corner of the selected cell and it will

duplicate the formula for the other rows beneath it.

Is there anyway that I can do this in VBA? The only way I can do it at

the moment is to paste the formula say 5000 rows and then remove the

unwanted rows at the bottom. The end of the data is changeable, so I

cannot just paste it to a certain cell number.

Your help appreciated,

Assuming there are NO blank rows in the data this should deal with

most of your problem:

Sheets("Sheet1").Range("A16").CurrentRegion.Copy Sheets("Sheet4"). _

Range("A65536").End(xlUp).Offset(1, 0)

Sheets("Sheet2").Range("A16").CurrentRegion.Copy Sheets("Sheet4"). _

Range("A65536").End(xlUp).Offset(1, 0)

Sheets("Sheet3").Range("A16").CurrentRegion.Copy Sheets("Sheet4"). _

Range("A65536").End(xlUp).Offset(1, 0)

Recording a macro to do this gave me:

Range("D9").Select

Selection.AutoFill Destination:=Range("D9:D31")

but of course this is now hardcoded so transform to:

Range("D9").Select

Selection.AutoFill Destination:=Range(Range("D9"), _

Range("D9").Offset(0, -1).End(xlDown).Offset(0, 1))

assuming you're copying formulas to the right of a block of data;

you'll have to change the offset values for other situations.

Didn't find what you were looking for?
Find more on Paste Formula help
Or get search suggestion and latest updates.

Related Topics:

- VBA Code for pasting a set of formulas down a data range
- formula help but not vba please
- Can Any one help me writing logic for the given formula
- Please Help me in this Formula
- Macro help newbie: VB conditional if statement to copy paste cell
- Help needed with Range Copy and Paste
- Help with Cut & Paste
- Help on copy and paste
- help me how to get rid of daily copy paste job
- Excel Macro help - conditional copy and paste from one worksheet to another
- New to VBA and need help with copy and paste
- clearing cells value without deleting the formula I've made in cell from excel formula , HOW?
- VBA Formula prob
- Getting a formula to copy from inside VBA
- Populate Formula(Function) in rows
- searching within formulas
- Formula using range name in VBA code
- Three Leveled Formula
- R1C1 Formula
- VBA or array formula?
- VBA code to add values in two cells which have formulas
- combining formula
- How to add several conditions for formula "SUMIF"
- Loop in formula
- Copy down formula with vba