Help Formatting cells after pasting from excel into word table

Vukodlak · #1 09-02-2021, 01:58 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Protect excel from formatting while coping and pasting	babaso_tawase	Excel	2	02-26-2017 06:17 AM
Pasting text from Excel cell into word without creating a table, and keeping the in-cell formatting	hanvyj	Excel Programming	0	08-28-2015 01:15 AM
Pasting Only Active Excel Cells With Link	JBell	Word	1	06-07-2015 03:53 AM
pasting from excel to word maintaining word formatting	fraserb	Word	2	09-01-2014 10:56 PM
borders disappear from merged cells when pasting from excel	joba	Word	0	09-10-2012 11:35 AM