0

I have some code that is supposed to copy a range of cells with data in excel and then paste it into a word document. The code works well, but the problem is that when it paste the data into word several blank pages appear after the table. the code is underneath here. Does anybody have an idea of how to fix it so that only the part with data gets copied and I can get rid of the blank pages?

Sub ExportToWord()
'Option Explicit

Dim WordApp As Word.Application
Dim myDoc As Word.Document
Dim WordTable As Word.Table
Dim SrcePath As String
Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range


    'Copies the specified range in excel
Set sht = Worksheets("Calculations")
Set StartCell = Range("M3")

'Refresh UsedRange
  Worksheets("Calculations").UsedRange

'Find Last Row
  LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'Select Range
  sht.Range("M3:R" & LastRow).Copy


    'Create an Instance of MS Word
  On Error Resume Next

    'Is MS Word already opened?
  If WordApp Is Nothing Then Set WordApp = CreateObject(class:="Word.Application")

    'Clear the error between errors
      Err.Clear

    'If MS Word is not already open then open MS Word
      If WordApp Is Nothing Then Set WordApp = CreateObject(class:="Word.Application")

    'Handle if the Word Application is not found
      If Err.Number = 429 Then
        MsgBox "Microsoft Word could not be found, aborting."
        GoTo EndRoutine
      End If

  On Error GoTo 0

    'Make MS Word Visible and Active
    WordApp.Visible = True
    WordApp.Activate

    'Create a New Document
    Set myDoc = WordApp.Documents.Add

    'Paste Table into MS Word
    myDoc.Paragraphs(1).Range.PasteExcelTable _
    LinkedToExcel:=False, _
    WordFormatting:=False, _
    RTF:=False

    'Autofit Table so it fits inside Word Document
    Set WordTable = myDoc.Tables(1)
    WordTable.AutoFitBehavior (wdAutoFitWindow)

    'Insert Header logo
    SrcePath = ""

    myDoc.Sections.Item(1).Headers(wdHeaderFooterPrimary) _
        .Range.InlineShapes.AddPicture (SrcePath)

    'Prompts users to save document
    WordApp.Documents.Save NoPrompt:=False

EndRoutine:
    'Optimize Code
    Application.ScreenUpdating = True
    Application.EnableEvents = True

    'Clear The Clipboard
    Application.CutCopyMode = False

    'Closes the Word application and the document
    On Error GoTo Err1:
    myDoc.Close
    WordApp.Quit
    Set WordApp = Nothing

Err1:

End Sub
Tiago Mussi
  • 801
  • 3
  • 13
  • 20
Sidvi
  • 101
  • 1
  • 2
  • 8
  • 1
    Do you have hidden rows between 3-rd and last rows? – AntiDrondert Nov 28 '17 at 09:43
  • I have hidden colums before column M but no hidden rows anywhere. – Sidvi Nov 28 '17 at 09:45
  • When you copy manualy (not with macro) does it happen as well? By the way, commentary are confusing (just a bit). – AntiDrondert Nov 28 '17 at 09:46
  • No it does not, i think its copying from 3 to 300 and that is why im getting the blank pages, but i cannot seem to figure out why it does this. – Sidvi Nov 28 '17 at 09:48
  • What is value of `LastRow` on `sht.Range("M3:R" & LastRow).Copy` ? – AntiDrondert Nov 28 '17 at 09:50
  • Right now it is 11 but it is dynamic, that is why im trying to get it to find the last row. – Sidvi Nov 28 '17 at 09:51
  • Could it be looking at coloums the the left of M and that is why its doing it? – Sidvi Nov 28 '17 at 09:53
  • 2
    `LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row` the problem is in this line (in my opinion). As I understood it calculates wrong number of rows to copy. Method of calculating last row by searching for last non-blank cell's row can result in undesirable values if your worksheet has more than one table. Consult [this](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) post to find the method you can implement. Most of the time I use *Find Last Row in a Column* and it works just fine. – AntiDrondert Nov 28 '17 at 10:05
  • I tried something from the post you mentioned and now it works perfectly. So thank you. – Sidvi Nov 28 '17 at 10:11

1 Answers1

0

There is a manual way to do it.

Select your table, click the right button of your mouse, and go to 'format cells'. Then select 'Number'-'Number'-'ok' like the picture I attached.

example

I hope it is helpful.

Shinhye
  • 1
  • 1