Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Paste Formula help

  Asked By: Carolina    Date: Jan 01    Category: MS Office    Views: 834
  

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,

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Milton Robinson     Answered On: Jan 01

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)

 
Answer #2    Answered By: Vinit Online     Answered On: Jan 01

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.




Tagged:      

 
 
 
 

Related Post