Hi,

I need help to do the following:

I have 100 rows of text messages on a sheet named "B_Msg".
I need to transfer every row of these text messages to another sheet "Msg" but before each message, I need to add a row in front with a text called "Name: " and another 2 rows behind this message (1st row contains text "##" and the 2nd row is a blank row).

An example of the end result would be:
Name:
my first message
##

Name:
my second message
##

I tried using For Next but it doesn't work. anyone can help?

Here you have it:

Sub Copy_Message()

Dim oSht As Worksheet 'Sheet - B_Msg Containing message
Dim oNewSht As Worksheet ' New sheet wehere the message needs to be copied
Dim i1 As Long ' Counter
Dim sMsg As String ' Message in oSht - B_Msg
Dim rNewRange As Range ' Range in the New sheet

Set oSht = Worksheets("B_Msg")
Set oNewSht = Worksheets.Add
Set rNewRange = oNewSht.Range("A2")

For i1 = 2 To oSht.Range("A:A").Cells.SpecialCells(xlCellTypeLas tCell).Row
    ' The above can be modified to suit your range
    sMsg = oSht.Cells(i1, 1).Value
    rNewRange.Value = "Name : "
    Set rNewRange = rNewRange.Offset(1, 0)
    rNewRange.Value = sMsg
    Set rNewRange = rNewRange.Offset(1, 0)
    rNewRange.Value = "##"
    Set rNewRange = rNewRange.Offset(2, 0)


Next i1



End Sub

Cheers
Shasur

http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

Hi Shasur,

thanks for your help. Your code worked wonderfully!

Thanks.

Hi All

This is my first ever post, I am also very new to VBA.

I need a code with some explanation and guidance of where to put the code. I tend to get confused, if a code must go in the userform, thisworkbook or in a module.

My question is as follows, let's say i have an excel database and i feed information thru a Userform, now let's say I come to the end of the row in the worksheet and want the the data to automatically go to the next worksheet and start updating from there, still keeping all other information in the first sheet, also if possible, some help with a code to retrieve any information from both sheets.

I thanks you in advance for your support.

Many Thanks

Regards

Mielo786