Code to copy a row of different formula down

[Jed Shields]

Hi guys,

I posted a question recently trying to use a standard formula based on a variable workbook name. The INDIRECT solution suggested worked fine until I realised that I'd need to have the additional workbooks open in order to return the results.

http://www.mrexcel.com/forum/showthread.php?t=580091

The reason I wanted to use a standard formula was so that I could use a piece of code I've written that would simply copy the formula from one cell and paste it accross and down for all cells within a range of rows and columns. Seeing as the formula now needs to be unique per column I need to change the code to copy the entire row of formula down instead of the one formula down and across. Here's my original code that copies a formula from B2 and pastes it down to the last row and across to the last column:

[FONT=Arial][SIZE=2]Sub CopyFormulaDown()[/SIZE][/FONT]
  
 [FONT=Arial][SIZE=2]Dim lngLastrow As Long
Dim rngTargetStart As  Range
Dim rngTargetEnd As Range[/SIZE][/FONT]
  
 [FONT=Arial][SIZE=2]lngLastrow =  Sheet3.Range("A65526").End(xlUp).Cells(1, 1).Row
lngLastcol = Sheet3.Cells(1,  Sheet3.Columns.Count).End(xlToLeft).Column[/SIZE][/FONT]
  
[FONT=Arial][SIZE=2] Set rngTargetStart = Sheet3.Range("B2")
Set rngTargetEnd =  Sheet3.Cells(lngLastrow, [FONT=Arial][SIZE=2]lngLastcol[/SIZE][/FONT])
  
 Sheet3.Range("B2").Copy Range(rngTargetStart, rngTargetEnd)
  
 [/SIZE][/FONT][FONT=Arial][SIZE=2]End Sub[/SIZE][/FONT]

What I assume I need is to change the Range("B2") in the last line to Range("B2:???"), where ??? is the last column eg AA2. I'm not sure how to do this dynamically though. Any ideas ...?

 

[Jed Shields]

Just to clarify, the code below works based on copying the formula down from row 2 all the way over to column 50. However, I'd like to change it so that it will dynamically alter the column from 50 to whatever in case I increase the number of columns of data in the future:

[FONT=Arial][SIZE=2]Sub CopyFormulaDown()[/SIZE][/FONT]
  
 [FONT=Arial][SIZE=2]Dim lngLastrow As Long
Dim rngTargetStart As  Range
Dim rngTargetEnd As Range[/SIZE][/FONT]
  
 [FONT=Arial][SIZE=2]lngLastrow =  Sheet3.Range("A65526").End(xlUp).Cells(1, 1).Row
lngLastcol = Sheet3.Cells(1,  Sheet3.Columns.Count).End(xlToLeft).Column[/SIZE][/FONT]
  
[FONT=Arial][SIZE=2] 
Set rngTargetStart = Sheet3.Range("B2")
Set rngTargetEnd =  Sheet3.Cells(lngLastrow, 50)
  
 Sheet3.Range("B2:AA2").Copy Range(rngTargetStart, rngTargetEnd)
  
 [/SIZE][/FONT][FONT=Arial][SIZE=2]End Sub[/SIZE][/FONT]

 

[Jed Shields]

Slight bump