Inserting multiple tables from Excel to Word VBA

Question

I am having an issue with my code, I am trying to copy 3 different table/cell locations within Excel and then Paste those values/tables within Word. I want to paste them directly below one another.

I keep getting a

Run Time Error of 5941. "Application Defined or Object Defined Error"

I tried looking up what this could mean but I am having no luck. I found this reference from this site: Run Time Error StackOverFlow but no luck figuring out my problem.

My Code:

Sub TalkToWord()
Dim wdApp As New Word.Application, wdDoc As Word.Document, wdRng As Word.Range
Dim myCat As Integer: myCat = InputBox("Enter your Category: ")
Dim myConfig As Long: myConfig = InputBox("Enter your Config #: ")
Dim myGradeName As String: myGradeName = InputBox("Enter your Grade Name: ")
Dim myDept As Integer: myDept = InputBox("Enter your Dept: ")
Dim myClass As Integer: myClass = InputBox("Enter your Class/Subclass: ")
Dim mySeason As String: mySeason = InputBox("Enter your Season Code: ")
myTimeFrame = Application.InputBox("Enter your Time Frame: ", FormatDateTime(Date, vbShortDate))

With wdApp
  .Visible = True
  Set wdDoc = .Documents.Add
  With wdDoc.Range
    .PageSetup.TopMargin = InchesToPoints(0.3)
    .ParagraphFormat.Alignment = wdAlignParagraphCenter
    .Font.Bold = True
    .Font.Underline = True
    .InsertAfter "FY 19 CAT " & myCat
    .InsertParagraphAfter
    Set wdRng = .Characters.Last
    With wdRng
      .ParagraphFormat.Alignment = wdAlignParagraphLeft
      .ParagraphFormat.LeftIndent = InchesToPoints(-0.7)
      .ParagraphFormat.SpaceAfter = 5
      .Font.Underline = False
      .Font.Size = 12
      .InsertParagraphAfter
      .InsertAfter ("Grade Number:")
      .InsertParagraphAfter
      .InsertAfter ("Config #:")
      .InsertParagraphAfter
      .InsertAfter ("Grade Name:" & myGradeName)
      .InsertParagraphAfter
      .InsertAfter (Chr(9) & "-" & "Dept:" & myDept)
      .InsertParagraphAfter
      .InsertAfter (Chr(9) & "-" & "Class/Subclass:" & myClass)
      .InsertParagraphAfter
      .InsertAfter (Chr(9) & "-" & "Season Code:" & mySeason)
      .InsertParagraphAfter
      .InsertAfter (Chr(9) & "-" & "TimeFrame:" & myTimeFrame)
      .InsertParagraphAfter
      .InsertAfter (Chr(9) & "-" & "Grade Type:" & myGradeType)
      .InsertParagraphAfter
      .InsertAfter (Chr(9) & "-" & "Index Breakpoint Bands by Volume Grade:")
      .InsertParagraphAfter
      .InsertParagraphAfter
      .Collapse wdCollapseEnd
      .InsertParagraphAfter
      .InsertBreak Type:=wdLineBreak
      .Collapse wdCollapseEnd
      .PageSetup.TopMargin = InchesToPoints(0.1)
     --- Worksheets("Sheet2").Range("A1", "B2").Copy
      .Paste
      .Tables(1).Rows.Alignment = wdAlignRowCenter
      .InsertBreak Type:=wdLineBreak
      .Collapse wdCollapseEnd
      .PageSetup.TopMargin = InchesToPoints(0.1)
      Worksheets("Sheet2").Range("K1", "Q2").Copy
      .Paste
      .Tables(2).Rows.Alignment = wdAlignRowCenter
      .InsertBreak Type:=wdLineBreak
      .Collapse wdCollapseEnd
      .PageSetup.TopMargin = InchesToPoints(0.1)
      Worksheets("Sheet2").Range("K4", "Q6").Copy
      .Paste
      .Tables(3).Rows.Alignment = wdAlignRowCenter
    End With
  End With
End With
End Sub

The error Occurs on the Line with the 3 dashes.

I am new to VBA so please go easy on me!

EDIT: It will Create the Word Document

dendarii dendarii · Accepted Answer · 2018-07-26T13:56:39

If this code is being run from the workbook you're copying things from, try changing the problem line from

Worksheets("Sheet2").Range("A1", "B2").Copy

to

ThisWorkbook.Worksheets("Sheet2").Range("A1", "B2").Copy

Inserting multiple tables from Excel to Word VBA

1 Answers