我如何打印并保存我的最后一个阵列? [英] How can I have my last array be printed and saved?

问题描述

我有一个脚本，该脚本根据列中的值何时更改(当下一组重复项开始时)映射数据并将其打印到模板，基本上停止并在单元格M2中停止打印并数组.; M3 类型交易.

I have a script that maps out data and prints an array to a template based on when values in a column change (when the next set of duplicates start), basically stops and prints and array when cells M2<>M3 type deal.

它将遍历并从模板中保存每个人的副本，但最后一组数据除外，它只是位于模板中，不会保存.如何编辑代码以通过 all 值，而不保留最后一组数据供我手动保存?

It goes through and saves off a copy from the template for everyone but the final set of data, it just sits in the template and doesn't save off. How can I edit my code to go through all values and not leave the last set of data for me to manually save off?

Option Explicit

Sub Main()

  Dim wb As Workbook
  Dim Report_Data, Last, Login
  Dim i As Long, j As Long, k As Long, a As Long
  Dim Destination_Rng As Range


  Workbooks.Open filename:="C:\Goal_Report_Template.xlsx"

  Set wb = Workbooks("Goal_Report_Template.xlsx")

  Set Destination_Rng = wb.Sheets("Sheet1").Range("A2")

  With ThisWorkbook.Sheets("Q1 report")
    Report_Data = .Range("W2", .Range("A" & Rows.Count).End(xlUp))
  End With

  wb.Activate

  Application.ScreenUpdating = False


  For i = 1 To UBound(Report_Data)

    If Report_Data(i, 14) <> Last Then

      If i > 1 Then

        Destination_Rng.Select

        wb.SaveCopyAs ThisWorkbook.Path & Application.PathSeparator & _
          ValidFileName(Login & " - " & Last & " - Goal Reporting.xlsx")
      End If

      Rows(1).Offset(1, 0).Resize(Rows.Count - 1).EntireRow.ClearContents

      Last = Report_Data(i, 14)
      Login = Report_Data(i, 13)

      j = 0
    End If

    a = 0
    For k = 1 To UBound(Report_Data, 2)
      Destination_Rng.Offset(j, a) = Report_Data(i, k)
      a = a + 1
    Next

    j = j + 1

  Next

End Sub

推荐答案

退出 i 循环后，您需要执行另一个SaveAs.通过将代码分成单独的子项，可以避免重复代码.

You need to perform another SaveAs after exiting the i loop. You can avoid duplicating code by breaking that out into a separate sub.

未经测试:

Sub Main()

    Dim wb As Workbook
    Dim Report_Data, Last, Login, Current
    Dim i As Long, j As Long, k As Long, a As Long
    Dim Destination_Rng As Range

    Set wb = Workbooks.Open(Filename:="C:\Goal_Report_Template.xlsx")
    Set Destination_Rng = wb.Sheets("Sheet1").Range("A2")

    With ThisWorkbook.Sheets("Q1 report")
        Report_Data = .Range("W2", .Range("A" & .Rows.Count).End(xlUp))
    End With

    Application.ScreenUpdating = False

    For i = 1 To UBound(Report_Data)

        Current = Report_Data(i, 14)

        If Current <> Last Then
            If i > 1 Then SaveCopy wb, Login, Last '<< save this one
            Destination_Rng.CurrentRegion.Offset(1, 0).ClearContents
            Login = Report_Data(i, 13)
            j = 0
        Else
            j = j + 1
        End If

        For k = 1 To UBound(Report_Data, 2)
            Destination_Rng.Offset(j, k - 1) = Report_Data(i, k)
        Next k

    Next i

    SaveCopy wb, Login, Last '<< save the last report

End Sub

Sub SaveCopy(wb As Workbook, Login, Last)
    wb.SaveCopyAs ThisWorkbook.Path & Application.PathSeparator & _
                  ValidFileName(Login & " - " & Last & " - Goal Reporting.xlsx")
End Sub

这篇关于我如何打印并保存我的最后一个阵列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！