Copying Formula's

[Mark]

Using CopyFromRecordset, my VB code copies data to "Sheet2" of a
spreadsheet.

"Sheet3" contains formula's that utilize the data in "Sheet2".

The number of records in "Sheet2" vary (one time there may be 100 and
the next time there may be 200).

The cells on "Sheet3, row 2" contain the formulas corresponding to
"Sheet2, row 2".

How can I get VB to copy the "Sheet3's" formula's down from row 2 to
the last row corresponding to the block of data in "Sheet2"?

Thanks for any suggestions.

Mark

 

[Bernie Deitrick]

Mark,

Worksheets("Sheet3").Range("2:2").Copy _
Worksheets("Sheet3").Range("3:" & _
Worksheets("Sheet2").Range("A65536").End(xlUp)(2).Row)

HTH,
Bernie
MS Excel MVP

 

[Pete McCosh]

Mark,

assuming from the location that both sheets have titles in
Row 1, try this.

Limit = application.worksheetfunction.counta(sheets("sheet
2").Range("A2:A65536"))
Sheets("Sheet 3").Range("A2:J2").copy destination:= sheets
("Sheet 3").Range(sheets("Sheet 3").Cells(3,1),sheets
("Sheet 3").cells(Limit+3,1))

Pete.

 

[Bernie Deitrick]

I'm sorry - I didn't actually test the code -
Worksheets("Sheet2").Range("A65536").End(xlUp)(2).Row)
should really be
Worksheets("Sheet2").Range("A65536").End(xlUp).Row)

I use the (2) to find the first empty cell, and it's gotten to be a
habit. ;-)

HTH,
Bernie
MS Excel MVP