Adjust current macro for extracting data


New member
Dec 23, 2013
Reaction score

I have a Macro that someone else wrote for me, and I'm trying to adjust it to work for a new project I have but I am not getting anywhere very fast. I have attached an example of my source spreadsheet and my destination spreadsheet. The source spreadsheet will change depending on how many agents etc we have, but the columns are always the same.

Sub AddFeeData()

    Dim AgentId     As Variant
    Dim FeeInfo     As Variant
    Dim Cell        As Range
    Dim cnt         As Integer
    Dim DataRng     As Range
    Dim DstRng      As Range
    Dim FilterRng   As Range
    Dim n           As Integer
    Dim Row         As Range
    Dim SrcRng      As Range
      ' Database worksheet in Master Ddata workbook.
        Set DstRng = Sheet1.Range("A1").CurrentRegion
        Set DataRng = Intersect(DstRng, DstRng.Offset(1, 0))
      ' Data Starts at row 2 if the Database is empty.
        If DataRng Is Nothing Then Set DataRng = DstRng
          ' Size the FeeInfo array to hold all the call audit info.
            ReDim AuditInfo(1 To 3, 1 To 40)
          ' Load the AuditInfo array will audit data from all 3 calls.
            For n = 1 To 88
                cnt = 0
                For Each Cell In SrcWks.Range("B15:C15, F15, J15")
                    cnt = cnt + 1
                    If cnt < 6 Then
                        FeeInfo(n, cnt) = Cell.Value
                        FeeInfo(n, cnt) = Cell.Offset(0, n - 1).Value
                    End If
                Next Cell
            Next n
            End If
But I am obviously not doing a lot correctly here

Source spreadsheet:
Destination Spreadsheet:
Here is how I am trying to map this information:

  • Destination Column(Header)---Source:
  • a) Audit Month---User entered Value
  • b) Audit Year---User Entered Value
  • c) Agent Name:---Source Column A
  • d) Month End Inv Fee Goal---Source Column C
  • e) MTD Fees---Source Column F
  • f) % EOM---Source Column J


  • Samplesourcedatasheet.xls
    53 KB · Views: 14
  • DestinationSpreadsheet.xlsb.xlsm
    19.7 KB · Views: 11