0

I am trying to make a program that reads a pivot table and pastes each field's data to a database with as little hard coding as possible.

The database has categories across the top row and the first day of each month for a year on the left side. The goal here is to take my spending data in a month (aggregated into a pivot table) and paste it to my database dynamically based on date and category.

Right now this program will work for most of the categories. Inexplicably it only wants to work on my one test pivot table. Other sheets or pivot tables are a no go, even if references are changed. It also hates the category "other" in column AJ for some reason and will not post to it, even if I change the name. Sometimes it decides something is no good and just breaks.

If an entry in the pivot table does not match a category it breaks, which is an issue as well.

Once the program breaks, I have to quit the VBA editor and re-open it to make the program stop error-ing.

Things typically break at the ColNum = Sheet5.Cells(2, 1).EntireRow... line, the TypeName field will sometimes have a category name that isn't even in the Pivot table anymore, but was the last test I ran.

Sub PivPasteNameData()
    'loop variables
    Dim I As Long
    Dim j As Long

    'pivot table variables
    Dim TypeName As String
    Dim TypeValue As Double

    'match variables
    Dim TodayDate As Range
    Dim DateRange As Range
    Dim CategoryRange As Range

    'paste variables
    Dim RowNum As Integer
    Dim ColNum As Integer

    'Setting match variables
    'this points to a cell on dashboard with today's date
    Set TodayDate = Sheet2.Range("C1")
    'this points to the left hand column of the database, it is the first of each month in ascending order
    Set DateRange = Sheet5.Range("A1:A100")
    'This points to all the categories across the top of the database
    Set CategoryRange = Sheet5.Range("A1:AZ2")

    'the index # will need to be variable
    Set PvTable = Sheet7.PivotTables(1)

    'first loop
    For I = 1 To PvTable.RowFields.Count
        'second loop
        For j = 1 To PvTable.RowFields(I).PivotItems.Count

            'setting the pivot tables field name (the type of the cost)
            TypeName = PvTable.RowFields(I).PivotItems(j)

            'getting the value of the cost
            TypeValue = Sheet7.PivotTables(1).GetPivotData("cost", "type", TypeName)

            'debugging
            'MsgBox TypeName
            'MsgBox TypeValue

            'find the corresponding date row numbner to the current date in the database
            RowNum = Application.WorksheetFunction.Match(TodayDate, DateRange, 1)
            'find the cost category column number in the database. For some reason I could NOT get Match working horizontally, my next attempt is going to use INDEX MATCH MATCH
            ColNum = Sheet5.Cells(2, 1).EntireRow.Find(What:=TypeName, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column

            'paste cost value in correct date/category
            Sheet5.Cells(RowNum, ColNum).Value = TypeValue
        Next j
    Next I
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Finlay McNally
  • 1
  • 1
    Please note that row counting variables need to be of type `Long` Excel has more rows than `Integer` can handle: `Dim RowNum As Long`. I recommend [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in using `Integer` at all. – Pᴇʜ Nov 22 '18 at 13:17
  • What does *"the program breaks"* mean? Does it not respond? This can also mean that it is still in one of your `For` loops (if there as a big amount of `RowFields`. Put a `DoEvents` right before `Next j` and `Next i` to keep Excel responsive in case of long loops. – Pᴇʜ Nov 22 '18 at 13:20

0 Answers0