none
Need sheet to be activated so that it can perform the row count RRS feed

  • Question

  • When I run the following macro, it always puts the data on the 18th row on the sheet it is supposed to go on!  I believe it is referencing the 18th row on the AT_A_Glance sheet still.  I need the data to be put on the correct row on the correct sheet.  

    How can I get it so that it goes to each sheet and activates it so that it counts down to the correct line?  

            

    Sub Transaction()

    Dim tithe As Worksheet
    Dim Debt_owed As Worksheet
    Dim Groceries As Worksheet
    Dim AT_A_Glance As Worksheet

    Set tithe = Sheets("Tithe")
    Set Debt_owed = Sheets("Debt owed")
    Set Groceries = Sheets("Groceries")
    Set AT_A_Glance = Sheets("At A Glance")


    If AT_A_Glance.Range("I17") = "Tithe" Then
        tithe.Activate
        tithe.Range("B" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = AT_A_Glance.Range("H19")
        tithe.Range("C" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = AT_A_Glance.Range("I19")
        tithe.Range("D" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = AT_A_Glance.Range("J19")
        tithe.Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = Date

    End If

    If AT_A_Glance.Range("I17") = "Debt Owed" Then
        Debt_owed.Activate
        Debt_owed.Range("B" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = AT_A_Glance.Range("H19")
        Debt_owed.Range("C" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = AT_A_Glance.Range("I19")
        Debt_owed.Range("D" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = AT_A_Glance.Range("J19")
        Debt_owed.Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = Date

    End If

    If AT_A_Glance.Range("I17") = "Groceries" Then
        Groceries.Activate
        Groceries.Range("B" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = AT_A_Glance.Range("H19")
        Groceries.Range("C" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = AT_A_Glance.Range("I19")
        Groceries.Range("D" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = AT_A_Glance.Range("J19")
        Groceries.Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = Date

    End If

    End Sub


    Monday, April 30, 2018 1:31 PM

All replies

  • Hi,

    I think your code works fine. Please provide your issue more detail.
    I have modified your code a little for readability.

    [code in sheet "At A Glance"]
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If (Intersect(Target, Range("I17")) Is Nothing) Then
            Exit Sub
        Else
            Call Transaction
        End If
    End Sub
    
    Private Sub Transaction()
        Dim tithe As Worksheet
        Dim Debt_owed As Worksheet
        Dim Groceries As Worksheet
        Dim AT_A_Glance As Worksheet
        ' ---
        Set tithe = Sheets("Tithe")
        Set Debt_owed = Sheets("Debt owed")
        Set Groceries = Sheets("Groceries")
        Set AT_A_Glance = Sheets("At A Glance")
        ' ---
        Select Case AT_A_Glance.Range("I17").Value
            ' -------------------
            Case Is = "Tithe"
                tithe.Activate
                tithe.Range("B" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = AT_A_Glance.Range("H19")
                tithe.Range("C" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = AT_A_Glance.Range("I19")
                tithe.Range("D" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = AT_A_Glance.Range("J19")
                tithe.Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = Date
            ' -------------------
            Case Is = "Debt Owed"
                Debt_owed.Activate
                Debt_owed.Range("B" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = AT_A_Glance.Range("H19")
                Debt_owed.Range("C" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = AT_A_Glance.Range("I19")
                Debt_owed.Range("D" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = AT_A_Glance.Range("J19")
                Debt_owed.Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = Date
            ' -------------------
            Case Is = "Groceries"
                Groceries.Activate
                Groceries.Range("B" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = AT_A_Glance.Range("H19")
                Groceries.Range("C" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = AT_A_Glance.Range("I19")
                Groceries.Range("D" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = AT_A_Glance.Range("J19")
                Groceries.Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Offset(1, 0).Value = Date
        End Select
    End Sub
    If this code (in sheet ""At A Glance") does not work as you expect.
    Please share your file via cloud storage (e.g. OneDrive, Dropbox, and so on).
    Remember to modify/edit your vital/private/corporate data before sharing.

    Regards,

    Ashidacchi -- http://hokusosha.com/

    • Edited by Ashidacchi Tuesday, May 1, 2018 3:59 AM
    Tuesday, May 1, 2018 3:55 AM