Overwrite existing entry macro

[muhleebbin]

I have the following code (thanks to the internet) in a spreadsheet that will slightly change in a new use. The code itself creates a new entry on a new line, is it possible to overwrite an existing entry on whichever line i'm currently viewing (there is other code that let's me go through the entries as they were entered). I would only need to be able to change one cell (G5) from the potential options of Approved, Pending Approval, Proposed.

Thank you in advance for your assistance!

Sub UpdateLogWorksheet()

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim myRng As Range
    Dim myCopy As String
    Dim myCell As Range
    
    'cells to copy from Input sheet - some contain formulas
    myCopy = "G3,G5,G7,F10,F11,F12,F13,F14,F15,F16,F18,F19,F20,F21,F22,F23,G10,G11,G12,G13,G14,G15,G16,G18,G19,G20,G21,G22,G23,H10,H11,H12,H13,H14,H15,H16,H18,H19,H20,H21,H22,H23"

    Set inputWks = Worksheets("Input")
    Set historyWks = Worksheets("Data")

    With historyWks
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    End With

    With inputWks
        Set myRng = .Range(myCopy)

        If Application.CountA(myRng) <> myRng.Cells.Count Then
            MsgBox "Please fill in all the cells!"
            Exit Sub
        End If
    End With

    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(nextRow, "B").Value = Application.UserName
        oCol = 3
        For Each myCell In myRng.Cells
            historyWks.Cells(nextRow, oCol).Value = myCell.Value
            oCol = oCol + 1
        Next myCell
    End With
    
    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
    
    Range("G3:I3").Select
    Selection.ClearContents
    Range("G5:I5").Select
    Selection.ClearContents
    Range("G7:I7").Select
    Selection.ClearContents
    Range("F10").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F11").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F12").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F13").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F14").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F15").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F16").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F18").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F19").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F20").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F21").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F22").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("F23").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G10").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G11").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G12").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G13").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G14").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G15").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G16").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G18").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G19").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G20").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G21").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G22").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G23").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H10").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H11").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H12").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H13").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H14").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H15").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H16").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H18").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H19").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H20").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H21").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H22").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("H23").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("G3:I3").Select
End Sub

 

[sijpie]

Not sure exactly what your workflow is.

The code above, as you say, will copy a bunch of data to a new line on the History sheet.
Now you want to modify the Gx (x is your active row) in the history sheet and select a value (Approved, etc)

When do you want to do this? After the data is copied? During the copy proces? Or do you just want a macro to go down the list of the Historysheet and set the approved status for each row (so nothing to do with the data copy)?


By the way, the last bit of your code can be made very much faster by not selecting each of the cells:

  Range("G3:I3").ClearContents
    Range("G5:I5").ClearContents
    Range("G7:I7").ClearContents
    Range("F10") = "0"
    Range("F11") = "0"
    etc

 

[muhleebbin]

Sijpie,

Thank you very much for your response!

The current workflow is as follows:

The change order or new budget information (bulletin) gets inputted using the form and code above by clicking a button 'Add to Data'. But it would be inputted using one of the options described: Proposed, Pending Approval, or Approved. There's other code and buttons on the sheet that let's you scroll through the previous entry(s) so if Proposed or Pending Approval was initially selected, i'd like this new code to overwrite that existing line should it change to Approved (or if the budget line items change) so that it's not a whole new input and a double entry and could use a button 'Update Data'

Hope this makes sense.

SVAR Actual - Budget.xlsm
	A	B	C	D	E	F
1	COR-PCO Number:
2
3	Change Order:
4
5	RFI/Bulletin/ROM:
6
7	Status:
8
9	Item #	Description	Material Unit Price	Shop Labor	Site Labor	Total
10	001	Main driveway curbing, not to exceed amount	-	-	-	-
11	002	Type 1 - Flagstone cladding and cap, not to exceed amount	-	-	-	-
Input

Cell Formulas
Range	Formula
F10:F11	F10	=SUM(C10:E10)

Cells with Data Validation
Cell	Allow	Criteria
D5:F5	Any value
D7:F7	List	=Lists!$Y$1:$Y$3

btw thanks for the suggestion! I was not looking forward to writing that out as it was as this new form will be significantly larger!

 

[muhleebbin]

Changing the field 'Status' (D7) would be the most likely and only update however would like this new button and code to update any possible field like C10:E11 just in case a proposed change order gets approved with a lower/higher number.

 

[sijpie]

So if the user selects a line in the history sheet and presses the update button, then that line should be placed back into the input sheet. On pressing the add button in the input sheet, then the original line in the history sheet should be updated (overwriting the time stamp and the user stamp with current time and user and any changes).

Perhaps you can copy the workbook without sensitive info (fake names etc) into dropbox or so? That will make it easier.

 

[muhleebbin]

ok i finally have a somewhat working copy of the workbook. Only the first section is currently working but this should give you a good idea of what is trying to be accomplished.

Add Input button adds to the data tab and hoping to add an Update Input button that will take the data that is pulled up and update accordingly.

NTE Tracking v3.xlsm

drive.google.com