Copying Formula's

  • Thread starter Thread starter Mark
  • Start date Start date
M

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
 
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,

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
 
Back
Top