问运行VBA脚本来自动化大型数据聚合

EN

Sub Example()
    Dim wsCopy As Worksheet
    Dim wsRR As Worksheet
    Dim wsRRPiv As Worksheet
    Dim lcopylastrow As Long
    Dim lRRLastRow As Long
    
    
    'Setting variables for report support workbook as well as download
    '
    
    Set wsCopy = ActiveWorkbook.Worksheets(1)
    Set wsRR = ThisWorkbook.Worksheets("Data")
    Set wsRRPiv = ThisWorkbook.Worksheets("Pivot")
    

    lcopylastrow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
    
    lRRLastRow = wsRR.Cells(wsRR.Rows.Count, "A").End(xlUp).Offset(1).Row
    
    
    ' Unmerging cells within download
    '
      wsCopy.Range("A3:G" & lcopylastrow).UnMerge
      
    ' This section of code will introduce the copy and pasting necessary to get the data into the consolidation of the report. As the report can only handle two separate Copy and pastes at a time, this first section will focus on getting the correct data sets for the left most columns (Sales & Cases)
    '
    
    Const wsCopy_STARTING_ROW As Integer = 6 'First row containing data in wsCopy
    Const wsRR_STARTING_ROW As Integer = 5 'First row containing data in wsRR
        
    Dim i As Long
    For i = wsCopy_STARTING_ROW To lcopylastrow
        'For each row of data in wsCopy
        
        'save a reference to the current row
        Dim CurrentRow As Range
        Set CurrentRow = wsCopy.Rows(i)
        
        'Grab all data values from the current row
        'ASSUMES DATA IS IN COLUMNS 1 to 7 (A:G)
        Dim CustomerID As String, CostCenter As String, ProductID As String, ProductName As String, WSV As String, LBS_Sold As String, Tubes_Sold As String
        CustomerID = CurrentRow.Cells(1).Value
        CostCenter = CurrentRow.Cells(2).Value
        ProductID = CurrentRow.Cells(3).Value
        ProductName = CurrentRow.Cells(4).Value
        WSV = CurrentRow.Cells(5).Value
        LBS_Sold = CurrentRow.Cells(6).Value
        Tubes_Sold = CurrentRow.Cells(7).Value
        
        'Try to find the destination row in wsRR using the CustomerID and ProductID
        Dim rDestination As Range
        Dim j As Long
        For j = wsRR_STARTING_ROW To lRRLastRow
            'For each row in wsRR
            'Compare the customerID and ProductID in wsRR to the ones from wsCopy
            'ASSUMES CUSTOMER ID is in wsRR COLUMN B and PRODUCT ID is in wsRR COLUMN D
            If wsRR.Cells(j, 2) = CustomerID And wsRR.Cells(j, 4) = ProductID Then
                Set rDestination = wsRR.Rows(j)
                Exit For
            End If
        Next
        If rDestination Is Nothing Then
            'If no destination row was found, add it to the bottom of wsRR
            Set rDestination = wsRR.Rows(lRRLastRow)
            'ASSUMES CUSTOMER ID to PRODUCT DESCRIPTION ARE IN wsRR COLUMNS B:E
            rDestination.Cells(2) = CustomerID
            rDestination.Cells(3) = CostCenter
            rDestination.Cells(4) = ProductID
            rDestination.Cells(5) = ProductName
            lRRLastRow = lRRLastRow + 1
        End If
        
        'At this point we have all the data from wsCopy and we have identified a destination row in wsRR
        'I don't know what to put in "METRIC" or what to do with Walled Sales Value, Sales Pounds, Tubes Sold
        'You can do stuff like
        'rDestination.Cells(1) = "WSV"
        'rDestination.Cells(10) = CDbl(WSV)
        'Which would put the string "WSV" in column A and then put the Walled Sales Value as a number into column J
    Next
End Sub