Get rid of apostrophe when pasting text string values

[Steve]

Following macro pastes code from a merged range in template to a series of
merged cells 24 rows apart. Code creates text string of varying length
depending on cells adjacent to each cell selection. Then copy and paste
values to turn code in the selection cell into pure text string. But string
has leading apostrophe that doesn't display but is included when we want to
copy the resultant string to another application. How to get rid of the
leading apostrophe using this macro?

Sub MonNotes()
Height = 24
For cnt = 0 To 19
Range("Template!P9:T10").Copy
Range("Report!Mon1").Offset(cnt * Height).Select
ActiveSheet.Paste
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Next
End Sub

 

[Patrick Molloy]

Try it another way ...

Sub MonNotes()
dim source as range
Height = 24
For cnt = 0 To 19
set source = worksheets("Template").Range("P9:T10")
with source
worksheets("Report").Range("Mon1").Offset(cnt *
Height).resize( .rows.count, .columns.count).Value = .Value
End With
Next
End Sub

 

[Joel]

You may need the signle quote at the beginning because if you have a string
thhat starts with a + or - excel will give an error because excel thinks this
is a formula and wants to put an equal sign in front of the string.

I provided two methods. the first will simply remove any single quote. If
you have single quotes in the rest of the string they will also get replaced.
the 2nd method only removes the single quote at the beginning fo the string
but requires more lines of code.


Sub MonNotes1()
Height = 24
For cnt = 0 To 19
Range("Template!P9:T10").Copy

Set DestRange = Range("Report!Mon1") _
.Offset(cnt * Height)
With DestRange
.Paste
.Copy
.PasteSpecial _
Paste:=xlPasteValues

.Replace what:="'", _
replacement:="", _
lookat:=xlPart

Application.CutCopyMode = False
End With
Next
End Sub

Sub MonNotes2()
Height = 24
For cnt = 0 To 19
Range("Template!P9:T10").Copy

Set DestRange = Range("Report!Mon1") _
.Offset(cnt * Height)
With DestRange
.Paste
.Copy
.PasteSpecial _
Paste:=xlPasteValues

For Each cell In DestRange
If Left(cell, 1) = "'" Then
cell = Mid(cell, 2)
End If
Next cell

Application.CutCopyMode = False
End With
Next
End Sub