All I can say is WOW and thanks -- this works perfectly!

"Jialiang Ge [MSFT]" wrote in message
...
Hello Gail,

In order to automate Excel to generate 2 rows per line of the original
data
and create for various number of rows, the most convenient way I think is
to write an Excel macro.

I write one macro for you according to your sample data. The macro can
generate a report in Sheet2(ColA~ColD) according to the data in
Sheet1(ColA~ColH). You may copy & paste the macro into Excel VBA Project
(Alt+F11). For your convenience, I also build a sample spreadsheet (xls)
to
demonstrate how to use the macro. You can download the demo(41751914.xls)
from this message with Outlook Express or Windows Mail.

I think this macro should be self-explanatory because I have added a lot
of
comments among codes. If you have any problem with it, feel free to let me
know.

Sub Generate()

'clear sheet2 (the target sheet)
Sheet2.Rows.Clear

'current row number in the source sheet
Dim sourceRowNum As Integer

For sourceRowNum = 1 To Sheet1.UsedRange.Rows.Count

Dim srcRowNumStr As String
srcRowNumStr = CStr(sourceRowNum)

'the first row in the target sheet
With Sheet2.Rows(sourceRowNum * 2 - 1)
'column A in the target sheet
.Cells(1, 1).Value2 = "3/31/08"

'column B in the target sheet
.Cells(1, 2).Formula = "=Sheet1!H" & srcRowNumStr &
"&""-4005-""&Sheet1!A" & srcRowNumStr

'column C in the target sheet
.Cells(1, 3).Formula = "=-Sheet1!D" & srcRowNumStr

'column D in the target sheet
.Cells(1, 4).Formula = "=Sheet1!B" & srcRowNumStr & "&"" -
""&Sheet1!C" & srcRowNumStr
End With

'the second row in the target sheet
With Sheet2.Rows(sourceRowNum * 2)
'column A in the target sheet
.Cells(1, 1).Value2 = "3/31/08"

'column B in the target sheet
.Cells(1, 2).Formula = "=Sheet1!H" & srcRowNumStr &
"&""-5360-""&Sheet1!A" & srcRowNumStr

'column C in the target sheet
.Cells(1, 3).Formula = "=Sheet1!E" & srcRowNumStr

'column D in the target sheet
.Cells(1, 4).Formula = "=Sheet1!B" & srcRowNumStr & "&"" -
""&Sheet1!C" & srcRowNumStr
End With

Next

End Sub

Regards,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
.

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.