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

Mark,

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

HTH,
Bernie
MS Excel MVP

"Mark" wrote in message
om...
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

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.

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

"Bernie Deitrick" wrote in message
...
Mark,

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

HTH,
Bernie
MS Excel MVP

"Mark" wrote in message
om...
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