Excel VBA to copy tables from 4 tabs in excel and paste in new word document - Formatting issues

LauraBlair

New Member
Joined
Feb 9, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I'm really hoping you may be able to help me. I have a VBA code that copies the tables from 4 tabs in the excel document, and then opens up a new word document and pastes into Word to form an 'Executive Summary'. This all works well, but the formatting of headings and boxes in the word doc that would require data input, is small and would like these cells in the table to merge so the text doesn't look so cluttered. I am struggling to get anything in the code to work in terms of this, I am starting to wonder if copy/pasting in tables is not the best way to do this. Could anyone please help? Thank you

VBA Code:
Sub Executive_Summary()
'
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim myTable As Table


With wdApp
  .Visible = True
  
  Set wdDoc = .Documents.Add
  
' Set up layout
  With wdDoc
    
' Copy the first range
    Range("FrontPage").Copy
    .Range.Characters.Last.Paste
    .Range.InsertAfter Chr(12) ' Insert new line before next table
    wdApp.Selection.SplitTable
    wdApp.Selection.TypeParagraph
    wdApp.Selection.TypeParagraph
    
' Before second table, insert column and format table
    Set myTable = wdDoc.Tables(1)
        With myTable
            .Columns.Add ' Adds a column to the right
            .AutoFitBehavior wdAutoFitWindow 'Autofit table to window
            .Range.Font.Size = 25
            .Range.Font.Bold = False
        End With
            
' Copy second range
    Range("Page2").Copy
    .Range.Characters.Last.Paste
    .Range.InsertAfter Chr(12) ' Insert new line
    
' Before third table, insert column and format table
    Set myTable = wdDoc.Tables(2)
        With myTable
            .Columns.Add ' Adds a column to the right
            .AutoFitBehavior wdAutoFitWindow 'Autofit table to window
            .Range.Font.Size = 9
            .Range.Font.Bold = False
        End With
    
' Copy third range
    Range("Page3").Copy
    .Range.Characters.Last.Paste
    .Range.InsertAfter Chr(12) ' Insert new line
    
' Before forth table, insert column and format table
    Set myTable = wdDoc.Tables(3)
        With myTable
            .Columns.Add ' Adds a column to the right
            .AutoFitBehavior wdAutoFitWindow 'Autofit table to window
            .Range.Font.Size = 9
            .Range.Font.Bold = False
        End With
    
' Copy forth  range
    Range("Page4").Copy
    .Range.Characters.Last.Paste
    .Range.InsertAfter Chr(12) ' Insert new line
    
' Insert column and format table
    Set myTable = wdDoc.Tables(4)
        With myTable
            .Columns.Add ' Add column to the right
            .AutoFitBehavior wdAutoFitWindow 'Autofit table to window
            .Range.Font.Size = 9
            .Range.Font.Bold = False
        End With
        
      End With
End With

End Sub
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

LauraBlair

New Member
Joined
Feb 9, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hi all, would anyone be able to assist with the above? I'm getting pretty desperate as my deadline is getting closer lol. I would really appreciate it. Thank you
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
3,022
Hi Laura. It doesn't seem like you're having any luck with this one. Not sure if this stuff helps, but U can copy and paste the XL table as a table....
Code:
Dim tbl As Range
Set tbl = Sheets("sheet1").ListObjects("Table1").Range
tbl.Copy
wdApp.ActiveDocument.Select
wdApp.Selection.PasteExcelTable _
        LinkedToExcel:=False, _
        WordFormatting:=False, _
        RTF:=False
To format...
Code:
Dim TblWdth As Double, ColWdth As Double, WordTbl As Object
'format table
With wdDOC.PageSetup
TblWdth = .PageWidth - .LeftMargin - .RightMargin - .Gutter
ColWdth = TblWdth / 4 'x4 columns
End With
Set WordTbl = .Tables(1)
With WordTbl
.AutoFormat Format:=16, applyborders:=True
.AutoFitBehavior (0)
.Columns.Width = ColWdth
End With
End With
HTH. Dave
 

LauraBlair

New Member
Joined
Feb 9, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hi Laura. It doesn't seem like you're having any luck with this one. Not sure if this stuff helps, but U can copy and paste the XL table as a table....
Code:
Dim tbl As Range
Set tbl = Sheets("sheet1").ListObjects("Table1").Range
tbl.Copy
wdApp.ActiveDocument.Select
wdApp.Selection.PasteExcelTable _
        LinkedToExcel:=False, _
        WordFormatting:=False, _
        RTF:=False
To format...
Code:
Dim TblWdth As Double, ColWdth As Double, WordTbl As Object
'format table
With wdDOC.PageSetup
TblWdth = .PageWidth - .LeftMargin - .RightMargin - .Gutter
ColWdth = TblWdth / 4 'x4 columns
End With
Set WordTbl = .Tables(1)
With WordTbl
.AutoFormat Format:=16, applyborders:=True
.AutoFitBehavior (0)
.Columns.Width = ColWdth
End With
End With
HTH. Dave
Thanks so much for you reply, formatting still not quite right, but definitely getting close with your help. Thanks
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
3,022
You are welcome. Thanks for posting your outcome. It seems that if U are putting 4 tables into the document, that the following code might come in handy. Place this in the code after all tables have been pasted. HTH. Dave
Code:
'prevent table from splitting page
Dim Otbl As Object, Ocel As Object
For Each Otbl In wdApp.ActiveDocument.Tables
Otbl.Range.Paragraphs.keepwithnext = True
For Each Ocel In Otbl.Rows.last.Range.Cells
Ocel.Range.Paragraphs.last.keepwithnext = False
Next Ocel
Next Otbl
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,134
Messages
5,835,601
Members
430,369
Latest member
pingel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top