I tried the watersev suggestion and that does almost what I want.
I have this function below - that works - except of course for adding the formulas.
Public Sub Make_my_new_sheet()
Dim InputSheet As Variant
Dim r As Long, yOff As Long, rowOut As Long
Dim s As String
InputSheet = Sheets("Input").UsedRange
yOff = 10 ' start pasting to row 10 of new sheet
For r = 2 To UBound(InputSheet, 1) 'skip row 1
rowOut = r - 1 + yOff
s = InputSheet(r, 1)
Sheets("Out").Cells(rowOut, 1) = s
s = InputSheet(r, 2)
Sheets("Out").Cells(rowOut, 2) = s
s = InputSheet(r, 6)
Sheets("Out").Cells(rowOut, 3) = s
s = InputSheet(r, 7)
Sheets("Out").Cells(rowOut, 4) = s
s = InputSheet(r, 9)
Sheets("Out").Cells(rowOut, 5) = s
Sheets("Out").Cells(rowOut, 6) = ""
' =======================================================
' This below is what I want to do ...
' Sheets("Out").Cells(rowOut, 7) = =IF ( NOT( ISBLANK(F11) ), IF (F11<E11,-(E11-F11),F11-E11), "")"
' Sheets("Out").Cells(rowOut, 8) = =IF ( NOT( ISBLANK(F11) ), ( ABS(E11-F11)/E11), "")
' Sheets("Out").Cells(rowOut, 9) = =IF ( AND( NOT( ISBLANK(F11) ), (H11>$C$7)), "???", "")
' ... with the exception that every E11 & F11 & H11 would increment (to E12 & F12 & H12, E13 & F13 & H13)
' every iteration of the loop.
' =======================================================
Sheets("Out").Cells(rowOut, 10) = ""
s = InputSheet(r, 12)
Sheets("Out").Cells(rowOut, 6 + 5) = s
s = InputSheet(r, 14)
Sheets("Out").Cells(rowOut, 7 + 5) = s
Sheets("Out").Cells(rowOut, 8 + 5) = r - 1
Next
End Sub
Thanks
Bookmarks