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
- 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
- Macro help newbie: VB conditional if statement to copy paste cell
- Help needed with Range Copy and Paste
- Help with Cut & Paste
- 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?
- combining formula
- VBA code to add values in two cells which have formulas
- How to add several conditions for formula "SUMIF"
- R1C1 Formula
- VBA or array formula?
- Loop in formula
- Copy down formula with vba
- Array Formula
- Ctrl+Shift+Arrow Fails To Select Cells When Used in Formula Bar
- 2003 - Moving to a formula address
- formula
- Any idea how to write formula?
- Formula with conditional text