Need some serious help

[whouthink]

I don't know if VBA can do this, but I have a range that I am trying to copy, and one of the cells has text data that I need to wrap at 40 characters. If I set the word wrap in Excel, it works fine as long as it is in a Microsoft Office program. However, I need the macro to wrap text at 40 characters and paste that way into Notepad or Word pad.

 

[Scott Huish]

What's the range and what column is your text that needs to wrap in?

 

[whouthink]

Sub CopyRange()
Sheets("Sheet1").Range("E2:E3").Copy
End Sub

The E3 cell needs to wrap.

 

[mcorriga]

Sub CopyRange()
Sheets("Sheet1").Range("E2:E3").Copy
End Sub

The E3 cell needs to wrap.

You can try this code. There are a couple things you may not like but it should be a good starting point.

1. It adds quotations around the text string. I'm sure there is a way to get rid of them but I didn't find it in a quick search

2. It wraps exactly at 40 (41 on the first and last lines because of the quotes) not considering whether it's in the middle of a word or not. Let me know if you need that and I'll try and help come up with some additional code to deal with that unless there is a predefined wrap function in vba you can use to bypass all of this which would be great.
Sub CopyRange()
Dim rawText, modifiedText As String
Dim lenText As Long
Dim j As Double
Dim startLoc As Integer
startLoc = 1
rawText = Sheets("Sheet1").Range("E3")
lenText = Len(Range("E3"))
j = Int(lenText / 40) + 1
Do Until j = 0
modifiedText = modifiedText + Mid(rawText, startLoc, 40) & vbCrLf
startLoc = startLoc + 40
j = j - 1
Loop
Sheets("Sheet1").Range("E4").Value = modifiedText
Sheets("Sheet1").Range("E4").Copy
End Sub

Hope this helps.

 

[mcorriga]

Oh and note that it copies the new contents into E4. If that is an issue change that to a different cell or adjust the method of storing that new string.

And also note that the final copy in my code does not include E2. Should be easy to correct. You could actually just have it paste the final contents back into E3 if you want.

 

[whouthink]

Definitely worked like a charm...I moved the data in E2 into E3 so it was no worries.

 

[whouthink]

OK... I ran into another slight problem (I am a VBA noob and just started last Monday...) I created a form to capture the data I needed to copy, and I can manipulate the command button in the form to place to a cell in a worksheet, but is there anyway that I can copy the data from the form using something similar to the above macro? I am able to use that macro with a command button on a separate sheet, but I don't want the end user to have to make that additional step.

 

[whouthink]

Nevermind...figured it out...

Private Sub BtnCopy_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("ICOMSnotes")

'find first empty row in database




'copy the data to the database

ws.Cells(3, 3).Value = "**" & TxtTime.Value & "**" & " Switch OK=" & cboSwitch.Value & " " & cboTechnology.Value & "=" & TxtLevels.Value & " // " & TxtFindings.Value & " " & cboStage.Value & " "

Dim rawText, modifiedText As String
Dim lenText As Long
Dim j As Double
Dim startLoc As Integer
startLoc = 1
rawText = Sheets("ICOMSNotes").Range("C3")
lenText = Len(Range("C3"))
j = Int(lenText / 39) + 1
Do Until j = 0
modifiedText = modifiedText + Mid(rawText, startLoc, 39) & vbCrLf
startLoc = startLoc + 39
j = j - 1
Loop
Sheets("ICOMSNotes").Range("A1").Value = modifiedText
Sheets("ICOMSNotes").Range("A1").Copy
End Sub


Private Sub BtnReset_Click()
Me.TxtTime.Value = Format(Time, "hh:mm", vbUseSystemDayOfWeek)
Me.cboSwitch.Value = ""
Me.cboTechnology.Value = ""
Me.TxtLevels.Value = ""
Me.TxtFindings.Value = ""
Me.cboStage.Value = ""
Me.BtnFeedbackY.Value = ""
Me.BtnFeedbackN.Value = ""
End Sub


Private Sub UserForm_Initialize()
Dim cSwitch As Range
Dim cTechnology As Range
Dim cStage As Range
Set ws = Worksheets("LookupLists")


For Each cSwitch In ws.Range("Switch")
With Me.cboSwitch
.AddItem cSwitch.Value
End With
Next cSwitch

For Each cTechnology In ws.Range("Technology")
With Me.cboTechnology
.AddItem cTechnology.Value
End With
Next cTechnology

For Each cStage In ws.Range("Stage")
With Me.cboStage
.AddItem cStage.Value
End With
Next cStage

Me.TxtTime.Value = Format(Time, "hh:mm", vbUseSystemDayOfWeek)
Me.TxtLevels.Value = " "
Me.cboSwitch.SetFocus

End Sub