Results 1 to 1 of 1

VBA - export to specific location in word

Threaded View

  1. 02-22-2020, 01:27 PM #1
    NPen
    NPen is offline
    Registered User
    Join Date
    03-12-2018
    Location
    Sofia
    MS-Off Ver
    2016
    Posts
    4

    VBA - export to specific location in word

    Hi Folks,

    I have a macro with assigned button. This macro is generating a new excel document. Could you help me how to put this table into e specific location in word document
    Public Sub MacroOPR()
'
' Macro3 Macro

On Error Resume Next
Application.ScreenUpdating = False
Sheets("ΞΟΠ").Delete
On Error GoTo 0
Worksheets.Add Before:=Sheets("Bal")
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
End With
ActiveSheet.Name = "ΞΟΠ"
ActiveWindow.DisplayGridlines = True
Range("A:A,E:E").ColumnWidth = 36
Range("B:B,F:F").ColumnWidth = 6.6
Range("C:C,D:D,G:G,H:H").ColumnWidth = 11

''lr2 = Sheets("ΞΟΠ").Cells(Rows.Count, "a").End(xlUp).Row
lr2 = Sheets("ΞΟΠ").Cells(1, "a")
ActiveWorkbook.Sheets("OPR").Select
For r = 1 To 22 Step 1
If Sheets("OPR").Range("K" & r).Value <> 0 Then
Sheets("OPR").Range("A" & r, "D" & r).Copy
With Sheets("ΞΟΠ").Range("A" & lr2 + 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
lr2 = lr2 + 1
End If
Next r
''lr3 = Sheets("ΞΟΠ").Cells(Rows.Count, "E").End(xlUp).Row
lr3 = Sheets("ΞΟΠ").Cells(1, "E")
ActiveWorkbook.Sheets("OPR").Select
For rr = 1 To 15 Step 1
If Sheets("OPR").Range("L" & rr).Value <> 0 Then
Sheets("OPR").Range("E" & rr, "H" & rr).Copy
With Sheets("ΞΟΠ").Range("E" & lr3 + 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
lr3 = lr3 + 1
End If
Next rr

lr4 = Sheets("ΞΟΠ").Cells(Rows.Count, "A").End(xlUp).Row
lr5 = Sheets("ΞΟΠ").Cells(Rows.Count, "E").End(xlUp).Row

Sheets("OPR").Range("A23: H23").Copy
If lr4 > lr5 Then
With Sheets("ΞΟΠ").Range("A" & lr4 + 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
Else
With Sheets("ΞΟΠ").Range("A" & lr5 + 1)
''.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
End If

lr2 = Sheets("ΞΟΠ").Cells(Rows.Count, "a").End(xlUp).Row
ActiveWorkbook.Sheets("OPR").Select
For r = 24 To 29 Step 1
If Sheets("OPR").Range("K" & r).Value <> 0 Then
Sheets("OPR").Range("A" & r, "D" & r).Copy
With Sheets("ΞΟΠ").Range("A" & lr2 + 1)
''.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats

End With
lr2 = lr2 + 1
End If
Next r

lr3 = Sheets("ΞΟΠ").Cells(Rows.Count, "E").End(xlUp).Row
ActiveWorkbook.Sheets("OPR").Select
For rr = 24 To 32 Step 1
If Sheets("OPR").Range("L" & rr).Value <> 0 Then
Sheets("OPR").Range("E" & rr, "H" & rr).Copy
With Sheets("ΞΟΠ").Range("E" & lr3 + 1)
''.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
lr3 = lr3 + 1
End If
Next rr
lr4 = Sheets("ΞΟΠ").Cells(Rows.Count, "A").End(xlUp).Row
lr5 = Sheets("ΞΟΠ").Cells(Rows.Count, "E").End(xlUp).Row

Sheets("OPR").Range("A33: H33").Copy
If lr4 > lr5 Then
With Sheets("ΞΟΠ").Range("A" & lr4 + 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
Else
With Sheets("ΞΟΠ").Range("A" & lr5 + 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
End If

lr2 = Sheets("ΞΟΠ").Cells(Rows.Count, "a").End(xlUp).Row
ActiveWorkbook.Sheets("OPR").Select
If Sheets("OPR").Range("K34").Value <> 0 Then
Sheets("OPR").Range("A34:D34").Copy
With Sheets("ΞΟΠ").Range("A" & lr2 + 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
End If
lr3 = Sheets("ΞΟΠ").Cells(Rows.Count, "E").End(xlUp).Row
ActiveWorkbook.Sheets("OPR").Select
If Sheets("OPR").Range("L34").Value <> 0 Then
Sheets("OPR").Range("E34:H34").Copy
With Sheets("ΞΟΠ").Range("E" & lr3 + 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
End If

lr4 = Sheets("ΞΟΠ").Cells(Rows.Count, "A").End(xlUp).Row
lr5 = Sheets("ΞΟΠ").Cells(Rows.Count, "E").End(xlUp).Row

Sheets("OPR").Range("A35: H35").Copy
If lr4 > lr5 Then
With Sheets("ΞΟΠ").Range("A" & lr4 + 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
Else
With Sheets("ΞΟΠ").Range("A" & lr5 + 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
End If

lr2 = Sheets("ΞΟΠ").Cells(Rows.Count, "a").End(xlUp).Row
ActiveWorkbook.Sheets("OPR").Select
For r = 36 To 38 Step 1
If Sheets("OPR").Range("K" & r).Value <> 0 Then
Sheets("OPR").Range("A" & r, "D" & r).Copy
With Sheets("ΞΟΠ").Range("A" & lr2 + 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
lr2 = lr2 + 1
End If
Next r
lr3 = Sheets("ΞΟΠ").Cells(Rows.Count, "E").End(xlUp).Row
ActiveWorkbook.Sheets("OPR").Select
If Sheets("OPR").Range("L36").Value <> 0 Then
Sheets("OPR").Range("E36:H36").Copy
With Sheets("ΞΟΠ").Range("E" & lr3 + 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
End If


lr4 = Sheets("ΞΟΠ").Cells(Rows.Count, "A").End(xlUp).Row
lr5 = Sheets("ΞΟΠ").Cells(Rows.Count, "E").End(xlUp).Row

''Sheets("OPR").Range("A23: H23").Copy
If Sheets("OPR").Range("K39").Value <> 0 Then
Sheets("OPR").Range("A39:D39").Copy
If lr4 > lr5 Then
With Sheets("ΞΟΠ").Range("A" & lr4 + 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
Else
With Sheets("ΞΟΠ").Range("A" & lr5 + 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
End If
End If

If Sheets("OPR").Range("L39").Value <> 0 Then
Sheets("OPR").Range("E39:H39").Copy
If lr4 > lr5 Then
With Sheets("ΞΟΠ").Range("E" & lr4 + 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
Else
With Sheets("ΞΟΠ").Range("E" & lr5 + 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
End If
End If

lr4 = Sheets("ΞΟΠ").Cells(Rows.Count, "A").End(xlUp).Row
lr5 = Sheets("ΞΟΠ").Cells(Rows.Count, "E").End(xlUp).Row

Sheets("OPR").Range("A40: H433").Copy
If lr4 > lr5 Then
With Sheets("ΞΟΠ").Range("A" & lr4 + 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
Else
With Sheets("ΞΟΠ").Range("A" & lr5 + 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
End If

Sheets("ΞΟΠ").Select
Columns("A:H").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With

End Sub

Sub MacroIzOPR()
'
' Macro2 Macro
'

Range("ProzOpr").ClearContents
''Range("B7:D22,B24:D28,B34:D34,B37:D38,F7:H22,F24:H31,F34:H34" _
).ClearContents
Range("B7").Activate
End Sub

Sub MySub()
'
' Macro
'
Call MacroBAL
Call MacroOPR
Call MacroOSK
Call MacroOPP

Sheets(Array("Αΰλΰνρ", "ΞΟΠ", "ΞΡΚ", "ΞΟΟ")).Move
Range("A1").Select
Application.DisplayAlerts = True

End Sub


    Also, can I use not e new document, but a word template? I have a few modules, which one does the same thing but for different sheets. How can I put all these tables from different sheets in one specific doc template?

    Regards,
    Nikola

    P.s. I'm sorry for the cyrillic
    Last edited by Leith Ross; 02-22-2020 at 05:20 PM. Reason: Added Code Tags
    Reply With QuoteRegister To Reply
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How do I export an Excel worksheet to Word with some specific formatting?
    By jasonfromchico in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-30-2015, 04:26 PM
  2. [SOLVED] Specify export pdf file location is location workbook is saved.
    By dantray02 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-30-2014, 01:13 PM
  3. [SOLVED] Using VBA to export to a specific location on Mac
    By absolut_cdn in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-03-2013, 05:21 AM
  4. Export/move cells of excel file to specific location on an image? [HARD QUESTION]
    By ProfessorOak in forum Excel General
    Replies: 3
    Last Post: 08-28-2012, 09:56 AM
  5. Export word file prompting a dialog box save location
    By Fossilized in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-05-2011, 03:04 AM
  6. How to hyperlink to a specific location in a Word document?
    By DEVAR in forum Excel General
    Replies: 6
    Last Post: 08-04-2006, 01:50 PM
  7. Data to a specific location into a word document
    By Adam in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-08-2005, 01:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1