Hey All,
I'm fairly new to all of this and just mostly putting together things I've seen others do at the moment but I don't know enough yet to know where I'm going wrong on this one.
I have the following word macro to copy some cells from Excel and paste it into the selected cells in a word table. Where it seem to being going wrong for me is when it is attempting to do the formatting afterwards. TargetRange.Select seems to also select the rest of the rows the selected cells were on and not just bringing back the original selection to format. Any help for this complete newbie would be amazing!
Example: I start with these cells selected
Capture — ImgBB
And then when I do TargetRange.Select it has highlighted the whole rows and formatted them as well.
Capture2 — ImgBB
Code:
Sub PasteToCells()
'
' PasteToCells Macro
'
'
Dim TargetRange As Range
If Selection.Cells.Count = 0 Then
'Quit if no cells in selection
MsgBox "No cells selected", vbCritical
Exit Sub
End If
On Error Resume Next
Set TargetRange = Selection.Range
TargetRange.Paste
TargetRange.Select
TargetRange.ParagraphFormat.Alignment = wdAlignParagraphLeft
TargetRange.Cells.VerticalAlignment = wdCellAlignVerticalBottom
TargetRange.Find.ClearFormatting
TargetRange.Find.Replacement.ClearFormatting
TargetRange.Font.Name = "Trebuchet MS"
TargetRange.Font.ColorIndex = wdBlack
With TargetRange.Find
.Text = " "
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
TargetRange.Find.Execute Replace:=wdReplaceAll
TargetRange.Select
End Sub