VBA Carriage Return based on text entered in cell

[smick]

Hi,

I have an excel sheet with a built in copy function. Problem is that when the users use the copy function is copies without the paragraph breaks the user entered through the Shift-Enter function. Is there a way to either:

1. Have the code read the text as entered by the user in the cell and enter a carriage return automatically at the combined entry of shift enter?
2. Have the copy macro maintain the same paragraph formatting as entered. Everything else is great on this part.
3. Something else yall know of that may accommodate that.

For reference text as it should appear:
"This should be line 1
This should be line 2

This is line 4 with a blank line at point 3"

This is how it does appear:
"This should be line 1This should be line 2This is line 4 with a blank line at point 3"

Its important of note that there is no prespecified character amount before a new line would be entered. It could be anything.

 

[OaklandJim]

I tried the super basic function below. If you "transfer" the value from one cell to another cell it retains the carriage returns as you want. No need to do a copy. BTW, shift enter moves to the previous cell. It does not add a CR. I use Alt-Shift to do what you want.

Function RetainCRs()
    
    Dim sInitialText As String
    
    With ThisWorkbook.Worksheets("Sheet1")
    
        sInitialText = .Range("B2").Value
    
        .Range("D2").Value = sInitialText

    End With

End Function

Does that help?

 

[Micron]

I don't see a problem with copy method either. This seems to work for me:

Sub KeepBreaks()
Application.EnableEvents = False
With Sheets("Sheet1")
     .Range("B7").Select
     Selection.Copy
     .Range("C7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
     .Range("C7").Activate
End With
Application.EnableEvents = True

End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

I have an excel sheet with a built in copy function.

Could we see the "copy function"

 

[smick]

Hi,

This is the copy code:

Sub Copy()
'
' Copy Macro
'
Application.ScreenUpdating = False
Worksheets("Underwriting Notes").Activate
ActiveSheet.Unprotect ("8675309")
If Range("C31") = "" Then
Range("D31").ClearContents
End If
ActiveSheet.Protect ("8675309")
If ActiveSheet.Protection.AllowFormattingRows = False Then
ActiveSheet.Protect AllowFormattingRows:=True
End If

Range("B3:G53").Select
Selection.Copy
End Sub

In terms of the copying its going to a system outside of microsoft so the formatting doesnt stay the same. When I use the copy macro I can paste into Word or Office fine and it retains all formatting but the output information looks like the second set when putting into the outside system.

 

[smick]

And I apologize, I realized I copied one I was working with to try and combined a couple things in error. Corrected code is:
ub Copy()
'
' Copy Macro
'
Application.ScreenUpdating = False
Worksheets("Underwriting Notes").Activate
Range("B3:G53").Select
Selection.Copy
If ActiveSheet.Protection.AllowFormattingRows = False Then
ActiveSheet.Protect AllowFormattingRows:=True
End If


End Sub

 

[Peter_SSs]

.. paragraph breaks the user entered through the Shift-Enter function

I assume that you mean Alt+Enter?

When I use the copy macro I can paste into Word or Office fine and it retains all formatting ..

In that case, the Copy is copying all the characters correctly

.. but the output information looks like the second set when putting into the outside system.

Then the issue is with the outside system and how it is treating the Alt+Enter characters.

I don't think that there is anything that you can do from the Excel side about that. You would need to investigate any options in the outside system.

BTW, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details.