• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Consolidated Pdf file using dynamic data from multiple worksheets

Glvba

New Member
Hello Chandoo members,

In my workbook I have data in worksheet "EQS". I have extracted rows of data (Criterion- Column D) from EQS sheet to individual sheets. This extracted data is transposed in each individual sheet that creates fix number of rows but variable number of columns. I was able to select this dynamic range and generate pdf on individual sheets. I needed your help in writing a code that auto-selects this dynamic range from each worksheet and generates consolidated pdf. I also wanted to exclude two sheets.



Code:
Sub CreateConsoPDF()


Dim sht As Worksheet
Dim LastRow AsLong
Dim LastColumn AsLong
Dim StartCell As Range


Set StartCell = Range("A1")

ForEach sht In ActiveWorkbook.Worksheets


'Find Last Row and Column LastRow = sht.Range("A1").CurrentRegion.Rows.Count
  LastColumn = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlValues, _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Column
'Select Range sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select


Next

With sht.PageSetup
    .Orientation = xlLandscape
    .Zoom = 85
    .Order = xlOverThenDown
    .PrintTitleColumns = sht.Columns("A:C").Address


EndWith

 ActiveWorkbook.Sheets("EQS").Visible = xlSheetHidden
 ActiveWorkbook.Sheets("Results").Visible = xlSheetHidden

ActiveWindow.RangeSelection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
   "c:\Book1.pdf", Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True


 ActiveWorkbook.Sheets("EQS").Visible = xlSheetVisible
 ActiveWorkbook.Sheets("Results").Visible = xlSheetVisible



EndSub
 
Hi !

Easy way : copy data to a consolidate worksheet then export it to a pdf file.

Use UsedRange property for each worksheet to copy …
 
Back
Top