Hi:

I have a workbook with about 150 worksheets in it. Each worksheet has
12 rows of text with a limit of 72 characters per row. (I use these
"templates" to enter text and then cut and paste the text from the 12
rows into an old mainframe database.)

In early 2005, the mainframe will be modified to allow text of any
width to be pasted into it. So, I'm going to want to take the contents
of the 12 rows and "merge" them into the first row. (It's just so much
easier allowing text to wrap...than using a 12 row text input system.)
I cannot use Excel's merge function as it only keeps the text in the
upper left cell.

I'm assuming this must be fairly simple to do. I simply want to create
a macro and apply it to a number of worksheets. (It's not a big deal
for me to apply the macro to each worksheet individually versus all at
once - as I will only need to do it once.)

Any help would be appreciated.
__________________________________________________

I'm also curious if the reverse is easy to do. i.e. If I made the
change now and started entering text into a single cell limited to (eg.
864 characters), is there a simple macro that I could use to
deconcatenate the single cell and place the results in a separate 12
row worksheet with each cell limited to 72 characters - or even simply
place the "results" into the Windows clipboard? I would not *need * the
functionality to split single words (with a hyphen) onto two lines. I'd
simply need whole words that couldn't fit on one line to be moved to
the next line. I realize that it may be the case that the full 864
characters might not fit on the 12 lines, but I could just have an
error message pop-up if that were the case.

Dave


--
snsd
------------------------------------------------------------------------
snsd's Profile: http://www.excelforum.com/member.php...o&userid=15910
View this thread: http://www.excelforum.com/showthread...hreadid=319443

snsd wrote:
I have a workbook with about 150 worksheets in it.
Each worksheet has 12 rows of text ..


Sub zMerge() ' merges A1:A12 into A1
Dim isheet%, irow&, icol%, s1$, s2$
icol = 1
For isheet = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(isheet).Select
MsgBox "Now doing sheet " & _
ActiveWorkbook.Sheets(isheet).Name
For irow = 1 To 12
s1 = ActiveWorkbook.Sheets(isheet).Cells(irow, icol)
If irow = 1 Then
s2 = s1
Else
If s1 < "" Then s2 = s2 & " " & s1
End If
ActiveWorkbook.Sheets(isheet).Cells(irow, icol) = ""
Next irow
ActiveWorkbook.Sheets(isheet).Cells(1, 1) = s2
Next isheet
End Sub


----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---