Vba to copy excel worksheet orientation to word document

[JD226]

Hi,

I have an excel workbook with 30+ worksheets. I have code which copies each worksheet into one new word document but my issue is that each worksheet has a different layout orientation whether it be landscape or portrait and i need that orientation to transfer to the word document as well. So the word document will be a combination of portrait and landscape layouts. Any ideas?

[westconn1]

are you meaning landscape as in the pagesetup.orientation property of the worksheet? or just the shape of data on the sheet?

to determine the shape of data, you probably need to sum the widths of the used columns and height of used rows, then compare to see which is greater

[JD226]

So each worksheet within the the workbook is set with the page setup property of either xlportrait or xllandscape. What I need is when my code copies every worksheet to the new word document that if the worksheet is xlLandscape = wdOrientLandscape and if its xlPortrait then in word its wdOrientPortrait. This is the code im using to copy it over.

Application.ScreenUpdating = False


Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Add


For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Copying data from " & ws.Name & "..."
ws.Range("Print_Area").Copy
wdDoc.paragraphs(wdDoc.paragraphs.Count).Range.InsertParagraphAfter
wdDoc.paragraphs(wdDoc.paragraphs.Count).Range.PasteExcelTable False, False, True
Application.CutCopyMode = False
wdDoc.paragraphs(wdDoc.paragraphs.Count).Range.InsertParagraphAfter

If Not ws.Name = Worksheets(Worksheets.Count).Name Then
With wdDoc.paragraphs(wdDoc.paragraphs.Count).Range
.InsertParagraphBefore
.Collapse Direction:=wdCollapseEnd
.InsertBreak Type:=wdPageBreak
End With
With wdApp.ActiveDocument.PageSetup
.TopMargin = wdApp.InchesToPoints(0.6)
.BottomMargin = wdApp.InchesToPoints(0.6)
.LeftMargin = wdApp.InchesToPoints(0.6)
.RightMargin = wdApp.InchesToPoints(0.6)
End With





End If




Next ws
Set ws = Nothing
Application.StatusBar = "Cleaning up..."


wdApp.Visible = True

Application.StatusBar = False




End Sub

[westconn1]

as paragraphs have no orientation, you need to add a new section each time you want to change the orientation within the document
each section has its own pagesetup

try like

wddoc.Sections.Add
wddoc.Sections(wddoc.Sections.Count).PageSetup.Orientation = ws.pagesetup.orientation - 1

you can test if the current section is the same as the worksheet and only add new section when required, or add new section for each worksheet regardless

[JD226]

Ahh Hah! Works great now! Thanks westconn1

[westconn1]

pls mark solved