Forum Discussion

hopr37 hotmail's avatar
hopr37 hotmail
Brass Contributor
Apr 19, 2018

couple problems. I know it's somewhere in my spreadsheet

My spreadsheet is a basic inventory sheet. It starts with an amount and you can subtract amounts and it gives you the remaining amount. It also copies changes onto a second worksheet. Anyway, my i...
spreadsheet cells
MrKickin Jody Bennett's avatar
MrKickin Jody Bennett
Copper Contributor
Aug 28, 2018

I looked over your sheet and made some slight changes to the VBA code.  Basically replace your code starting from "On Error Resume Next" with the following code:

 

 On Error Resume Next
 Application.ScreenUpdating = False
 
 If Not Intersect(Target, Range("C:C, A:A, F:F")) Is Nothing Then
 
 Dim r2 As Integer
 
 r2 = Application.WorksheetFunction.CountA(Sheets("CopperHistory").Range("C:C"))
 'Not necessary to create a variable for each column since each new entry will be on the same row no matter which column it's in
 
 Sheets("CopperHistory").Range("B2").Offset(r2).Value = Sheets("Copper").Range("A" & Target.Row).Value
 Sheets("CopperHistory").Range("C2").Offset(r2).Value = Sheets("Copper").Range("C" & Target.Row).Value
 Sheets("CopperHistory").Range("D2").Offset(r2).Value = Sheets("Copper").Range("F" & Target.Row).Value
 
 


 
 End If

 Application.ScreenUpdating = True
 On Error GoTo 0
'End Sub
Selection.Offset(1, 0).Select

'This will advance your target to the next row
 


End Sub

Resources