Hi, I would like to VBA create a print range as such: From cell A3 down to a row number specified in cell A1, and then to the rightmost column that has data in it. However there will be hidden columns between column A and the last column. Thanks
Sub Maybe()
Dim lc As Long, GT As Long
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
GT = Cells(1, 1).Value
ActiveSheet.PageSetup.PrintArea = Range("A3:A" & GT).Resize(, lc).Address
ActiveSheet.PrintPreview '<---- Change to print when you're happy.
End Sub
Sub PRINT_RANGE_AUTO()
Dim lc As Long, GT As Long
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
GT = Cells(1, 1).Value
ActiveSheet.PageSetup.PrintArea = Range("A3:A" & GT).Resize(, lc).Address
With ActiveSheet
.PageSetup.PrintArea = PrintAreaString
.PageSetup.Orientation = xlLandscape
.PageSetup.Zoom = False
If Range("A1") <= 45 Then
.PageSetup.FitToPagesWide = 1
.PageSetup.FitToPagesTall = 1
.PageSetup.LeftMargin = 36
.PageSetup.TopMargin = 72
.PageSetup.RightMargin = 36
.PageSetup.BottomMargin = 36
Else
.PageSetup.FitToPagesWide = 1
.PageSetup.FitToPagesTall = False
.PageSetup.LeftMargin = 36
.PageSetup.TopMargin = 72
.PageSetup.RightMargin = 36
.PageSetup.BottomMargin = 36
ActiveSheet.UsedRange.Font.Size = 12
.UsedRange.RowHeight = 22.75
End If
End With
End Sub
Dim shArr, i As Long '<----- Add to your Dim Statements
shArr = Array("Sheet4", "Sheet7", "Sheet12", "Sheet65") '<---- Sheets that the macro should work on. Change to your requirements
For i = Lbound(shArr) To Ubound(shArr)
With Sheets(shArr(i))
'Your code here
End With
Next i
End Sub
Sub PRINT_RANGE_AUTO_PREVIEW2()
Dim lc As Long, GT As Long
Dim shArr, i As Long
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
GT = Cells(1, 1).Value
shArr = Array("MASTER FORM", "PACK SLIP", "INVOICE") '<---- Sheets that the macro should work on. Change to your requirements
For i = LBound(shArr) To UBound(shArr)
With Sheets(shArr(i))
.PageSetup.PrintArea = Range("A3:A" & GT).Resize(, lc).Address
.PageSetup.Orientation = xlLandscape
.PageSetup.Zoom = False
If Range("A1") <= 45 Then
.PageSetup.FitToPagesWide = 1
.PageSetup.FitToPagesTall = 1
.PageSetup.LeftMargin = 36
.PageSetup.TopMargin = 72
.PageSetup.RightMargin = 36
.PageSetup.BottomMargin = 36
Else
.PageSetup.FitToPagesWide = 1
.PageSetup.FitToPagesTall = False
.PageSetup.LeftMargin = 36
.PageSetup.TopMargin = 72
.PageSetup.RightMargin = 36
.PageSetup.BottomMargin = 36
End If
End With
Next i
End Sub
Sub PRINT_RANGE_AUTO_PREVIEW2()
Dim lc As Long, GT As Long
Dim shArr, i As Long
shArr = Array("MASTER FORM", "PACK SLIP", "INVOICE") '<---- Sheets that the macro should work on. Change to your requirements
For i = LBound(shArr) To UBound(shArr)
With Sheets(shArr(i))
lc = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
GT = .Cells(1, 1).Value
.PageSetup.PrintArea = Range("A3:A" & GT).Resize(, lc).Address
.PageSetup.Orientation = xlLandscape
.PageSetup.Zoom = False
If Range("A1") <= 45 Then
.PageSetup.FitToPagesWide = 1
.PageSetup.FitToPagesTall = 1
.PageSetup.LeftMargin = 36
.PageSetup.TopMargin = 72
.PageSetup.RightMargin = 36
.PageSetup.BottomMargin = 36
Else
.PageSetup.FitToPagesWide = 1
.PageSetup.FitToPagesTall = False
.PageSetup.LeftMargin = 36
.PageSetup.TopMargin = 72
.PageSetup.RightMargin = 36
.PageSetup.BottomMargin = 36
End If
End With
Next i
End Sub