VBA question - Trying to write information into a new row in a different sheet but it keeps overwriting old information

[aurelius]

Hello all! Title says it all but basically I am trying to create a worksheet in which everytime I input information into Sheet 1, it is copied into Sheet 2. I want to have each entry in succession on Sheet 2 such that my first entry would be on Row 2, second on Row 3, third on Row 4, etc. However, everytime I put something new in Sheet 1, it just overrides the information in Sheet 2.

Basically I type in ticket sales in sheet 1, it calculates the prices and keeps a transaction log in sheet 2. But everytime I do a new ticket sale, it just overwrites the previous transaction witht he new transaction information. I've pasted my VBA below, if you see where I went wrong please let me know! I am tearing my hair out.

Sheet 1 VBA:

Option Explicit

'Form level variables - used in more than one event
Dim intAdult As Integer
Dim intStudentSenior As Integer
Dim intBalcony As Integer
Dim intChild As Integer
Dim sngAmountDue As Single
Dim sngChangeDue As Single
Dim sngAmountReceived As Single
Const conAdultPrice As Single = 25
Const conStudentSeniorPrice As Single = 20
Const conBalconyPrice As Single = 15
Const conChildPrice As Single = 0


Private Sub cmdNext_Click()
    Dim intTheaterSeats As Integer
    Dim intBalconySeats As Integer
   
    intTheaterSeats = Range("Theater_Seats").Value
    intBalconySeats = Range("Balcony_Seats").Value
    
    intTheaterSeats = intTheaterSeats - intAdult - intStudentSenior - intChild
    intBalconySeats = intBalconySeats - intBalcony
    
    Range("Theater_Seats") = intTheaterSeats
    Range("Balcony_Seats") = intBalconySeats
      
    Range("Order").Select
    Selection.ClearContents
    Range("Adults").Select
  
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim strCurrentCell As String
    Dim strB10 As String
    Dim strB12 As String
    Dim intTheaterSeats As Integer
    Dim intBalconySeats As Integer
    Dim intRow As Integer
    Dim strMessage As String
    
    strCurrentCell = ActiveCell.Address
    strB10 = "$B$10"
    strB12 = "$B$12"
    
    
    If strCurrentCell = strB10 Then
        'Assignment of values from worksheet to variables
        intAdult = Range("Adults").Value
        intStudentSenior = Range("Student_Senior").Value
        intBalcony = Range("Balcony").Value
        intChild = Range("Child").Value
        intTheaterSeats = Range("Theater_Seats").Value
        intBalconySeats = Range("Balcony_Seats").Value
        
        intTheaterSeats = intTheaterSeats - intAdult - intStudentSenior - intChild
        intBalconySeats = intBalconySeats - intBalcony
        
        If intTheaterSeats < 0 Then
            MsgBox "Sorry there are not enough seats available"
            Range("Adults").Select
            Exit Sub
        End If
        
        If intBalconySeats < 0 Then
            MsgBox "Sorry there are not enough Balcony seats available"
            Range("Adults").Select
            Exit Sub
        End If


        'Calculation of AmountDue based on regular ticket prices
        sngAmountDue = intAdult * conAdultPrice + intStudentSenior _
        * conStudentSeniorPrice + intBalcony * conBalconyPrice _
        + intChild * conChildPrice
        
        'Display of Amount Due in cell with currency format
        Range("Amount_Due") = FormatCurrency(sngAmountDue)
        Range("Amount_Received ").Select
    End If


    strMessage = "Check your entry for Amount Received," _
        & " amount actually received, and if" _
        & " necessary ask for more money"
    
    If strCurrentCell = strB12 Then
        'Assignment of Values from Work Sheet Variable
        sngAmountReceived = Range("Amount_Received").Value
        
        If sngAmountDue > sngAmountReceived Then
            MsgBox strMessage, vbOKOnly, "Error"
            Range("Amount_Received").Select
            Exit Sub
    
        Else


            'Calculation of ChangeDue based on AmountRecieved
            sngChangeDue = sngAmountReceived - sngAmountDue
            
            'Display of Change Due in cell with currency format
            Range("Change_Due") = FormatCurrency(sngChangeDue)
            
            intRow = intRow + 1
            If intRow = 1 Then
                intRow = intRow + 1
            End If
            
            Worksheets("Sheet2").Range("A" & intRow) = intRow - 1
            Worksheets("Sheet2").Range("B" & intRow) = intAdult
            Worksheets("Sheet2").Range("C" & intRow) = intStudentSenior
            Worksheets("Sheet2").Range("D" & intRow) = intBalcony
            Worksheets("Sheet2").Range("E" & intRow) = intChild
            Worksheets("Sheet2").Range("F" & intRow) = sngAmountDue
            Worksheets("Sheet2").Range("G" & intRow) = sngAmountReceived
            
            cmdNext.Activate
        End If
    End If
    
End Sub

Sheet 2 code:

Private Sub cmdSummary_Click()


'Declare Variables
Dim intCount As Integer
Dim i As Integer
Dim intAdult As Integer
Dim intStudentSenior As Integer
Dim intBalcony As Integer
Dim intChild As Integer
Dim sngAmountDue As Single
Dim sngAmountReceived As Single


'Count number of records in worksheet
Range("A1").Select
ActiveCell.CurrentRegion.Select
intCount = Selection.Rows.Count


'Loop to accumulate values from worksheet
For i = 2 To intCount
    If Range("A" & i) > "" Then
        intAdult = intAdult + Range("B" & i).Value
        intStudentSenior = intStudentSenior + Range("C" & i).Value
        intBalcony = intBalcony + Range("D" & i).Value
        intChild = intChild + Range("E" & i).Value
        sngAmountDue = sngAmountDue + Range("F" & i).Value
        sngAmountReceived = sngAmountReceived + Range("G" & i).Value
    End If
Next i


'Writes accumulated values to row below the data
Range("B" & intCount + 1) = intAdult
Range("C" & intCount + 1) = intStudentSenior
Range("D" & intCount + 1) = intBalcony
Range("E" & intCount + 1) = intChild
Range("F" & intCount + 1) = FormatCurrency(sngAmountDue)
Range("G" & intCount + 1) = FormatCurrency(sngAmountReceived)
Range("A1").Select


End Sub

Let me know if you need any information. Thanks! Sorry if I'm not descriptive, I am so tired I've been tearing my hair out at this for hours

 

[JLGWhiz]

This could be your problem:

If intRow = 1 Then
     intRow = intRow + 1
End If

If intRow is not = 1 Then intRow will not increment. But with a slight change to:

If intRow >= 1 Then
    intRow = intRow + 1
End If

But if you want intRow to equal 1 at least once I think I would write that section like this:

If intRow = "" Then
 intRow = 1
Else 
 intRow = intRow + 1
End If

 

[aurelius]

I ended up doing this, which fixed the problem of the row copying over the previous data:

 Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = intRow            Worksheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = intAdult
            Worksheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1).Value = intStudentSenior
            Worksheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Offset(1).Value = intBalcony
            Worksheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1).Value = intChild
            Worksheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Offset(1).Value = sngAmountDue
            Worksheets("Sheet2").Range("G" & Rows.Count).End(xlUp).Offset(1).Value = sngAmountReceived

But now intRow (the transaction number) keeps displaying as one no matter what I do... I even tried to do

Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = intRow + 1

(adding a 1 to introw) but it doesn't do anything... what the hell :|

 

[JLGWhiz]

I ended up doing this, which fixed the problem of the row copying over the previous data:

But now intRow (the transaction number) keeps displaying as one no matter what I do... I even tried to do

Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = intRow + 1

(adding a 1 to introw) but it doesn't do anything... what the hell :|

Take a look at the third code snippet in my previous post. Try to use that principle in writing your statement.