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.
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