时间戳

首页 > 解决方案 > VBA .Pagesetup 行不更新

excel - VBA .Pagesetup 行不更新

问题描述

我编写了这个例程来打印每个文件的数据透视表。( codeConv) 使用 F8 逐步测试时，一切正常。最后一行和最后一列工作正常，通过调用例程Call PrintLoyerPivot(codeConv)或使用 F5 运行它时，PrintArea = ws.Range("A1:" & ColumnLetter & lastRowSAS).Address 似乎不起作用。它总是只打印第一行，就好像 Print Area 是ws.Range("A1:" & ColumnLetter & 1")

VBA：

   Sub PrintLoyerPivot(ByVal codeConv As String)

   Dim filter As PivotItem
   Dim ws As Worksheet
   Dim lastRowSAS As Long
   Dim lastColSAS As Long
   
   Application.Calculation = xlCalculationManual

   ThisWorkbook.RefreshAll


   ThisWorkbook.Worksheets("loyer_pivot").PivotTables("LoyerParCode").PivotFields("CodeConv").ClearAllFilters

   For Each filter In ThisWorkbook.Worksheets("loyer_pivot").PivotTables("LoyerParCode").PivotFields("CodeConv").PivotItems
       If filter.Caption = codeConv And filter.RecordCount > 0 Then GoTo SetFilter1
   Next filter
   GoTo notfound

   SetFilter1:
       ThisWorkbook.Worksheets("loyer_pivot").PivotTables("LoyerParCode").PivotFields("CodeConv").CurrentPage = codeConv

       Set ws = ThisWorkbook.Sheets("loyer_pivot")
       lastRowSAS = [LOOKUP(2,1/(A1:A65536<>""),ROW(A1:A65536))]
       lastColSAS = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
       ColumnLetter = Split(Cells(1, lastColSAS).Address, "$")(1)

       ws.Calculate
       ws.Range("A1:" & ColumnLetter & lastRowSAS).Columns.AutoFit


   With ws.PageSetup
           .PrintArea = ws.Range("A1:" & ColumnLetter & lastRowSAS).Address
           .PrintQuality = 600
           .Orientation = xlLandscape
           .PaperSize = xlPaperA4
           .FitToPagesWide = 1
           .ScaleWithDocHeaderFooter = True
           .AlignMarginsHeaderFooter = True
   End With

  ws.PrintOut
  Application.Calculation = xlCalculationAutomatic

  notfound:

  End Sub

我在另一个数据透视表打印输出脚本中遇到了同样的问题。但是最后一行通常太高，所以它会打印它应该打印的内容以及一些额外的空白页。

标签： excelvba

解决方案

感谢 Ike，它现在可以这样工作：

我刚刚添加了ws.activate之前lastRowSAS = LOOKUP(2,1/(A1:A65536<>""),ROW(A1:A65536))]

Sub PrintLoyerPivot(ByVal codeConv As String)

   Dim filter As PivotItem
   Dim ws As Worksheet
   Dim lastRowSAS As Long
   Dim lastColSAS As Long
   
   Application.Calculation = xlCalculationManual

   ThisWorkbook.RefreshAll
ThisWorkbook.Worksheets("loyer_pivot").PivotTables("LoyerParCode").PivotFields("CodeConv").ClearAllFilters

   For Each filter In ThisWorkbook.Worksheets("loyer_pivot").PivotTables("LoyerParCode").PivotFields("CodeConv").PivotItems
       If filter.Caption = codeConv And filter.RecordCount > 0 Then GoTo SetFilter1
   Next filter
   GoTo notfound

   SetFilter1:
       ThisWorkbook.Worksheets("loyer_pivot").PivotTables("LoyerParCode").PivotFields("CodeConv").CurrentPage = codeConv

       Set ws = ThisWorkbook.Sheets("loyer_pivot")
       ws.activate
       lastRowSAS = [LOOKUP(2,1/(A1:A65536<>""),ROW(A1:A65536))]
       lastColSAS = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
       ColumnLetter = Split(Cells(1, lastColSAS).Address, "$")(1)

       ws.Calculate
       ws.Range("A1:" & ColumnLetter & lastRowSAS).Columns.AutoFit


   With ws.PageSetup
           .PrintArea = ws.Range("A1:" & ColumnLetter & lastRowSAS).Address
           .PrintQuality = 600
           .Orientation = xlLandscape
           .PaperSize = xlPaperA4
           .FitToPagesWide = 1
           .ScaleWithDocHeaderFooter = True
           .AlignMarginsHeaderFooter = True
   End With

  ws.PrintOut
  Application.Calculation = xlCalculationAutomatic

  notfound:

  End Sub

推荐阅读