August 24, 2022
Hi,
I have a excel with this code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 12 Or Target.Columns.Count > 1 Then _
If Target.Column <> 14 Or Target.Columns.Count > 1 Then _
If Target.Column <> 16 Or Target.Columns.Count > 1 Then _
Exit Sub
Dim tmp As Variant
tmp = Cells(Target.Row, 16).Formula 'save contents
On Error GoTo Enable_Events
Application.EnableEvents = False
Cells(Target.Row, 16) = "#$"
Range("A1").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
Cells(Application.Match("#$", Columns(16), 0), 1).Select
Range("L1").Sort Key1:=Range("L1"), Order1:=xlAscending, Header:=xlYes
Cells(Application.Match("#$", Columns(16), 0), 12).Select
Range("N1").Sort Key1:=Range("N1"), Order1:=xlAscending, Header:=xlYes
Cells(Application.Match("#$", Columns(16), 0), 14).Select
Range("P1").Sort Key1:=Range("P1"), Order1:=xlDescending, Header:=xlYes
Cells(Application.Match("#$", Columns(16), 0), 16).Select
Cells(Selection.Row, 16) = tmp 'restore contents
Enable_Events:
Application.EnableEvents = True
End Sub
now, I want to create new column, like this:
if Q1<G1, then insert new row below, then copy all formatting and numbers there, and G2=G1-Q1)
How can I use something like that in the Vba code?
I attached file here
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
The code you have is triggered by Worksheet Change event.
You want to insert new rows based on sheet changes as well?
If column Q should insert rows, then the code should look like this:
If Not Intersect(Target, Range("Q2:Q1000")) Is Nothing Then
If Cells(Target.Row, "G") <> 0 And Target < Cells(Target.Row, "G") Then
Target.Offset(1, 0).EntireRow.Insert
Cells(Target.Row, "G").Offset(1, 0).Formula = "=" & Cells(Target.Row, "G").Address(False, False) & "-" & Target.Address(False, False)
End If
End If
You should also allow column Q to trigger the code:
If Target.Column <> 12 Or Target.Columns.Count > 1 Then _
If Target.Column <> 14 Or Target.Columns.Count > 1 Then _
If Target.Column <> 17 Or Target.Columns.Count > 1 Then _
Exit Sub
August 24, 2022
appreciate for answering me
Look at this file:
I always use Q column for green rows, when I enter the quantity less than G column, I want to insert row below that cells
now it happened at the end and newG=Gup-Qup (G10=G9-Q9) but for example: I enter in Q4, so new row insert in below, and G5=G4-Q4, and all information in row 4 copy to row 5
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Ok, added code to copy row formats and values:
If Cells(Target.Row, "G") <> 0 And Target < Cells(Target.Row, "G") Then
Target.Offset(1, 0).EntireRow.Insert
Range("A" & Target.Row & ":Q" & Target.Row).Copy _
Destination:=Range("A" & Target.Row + 1 & ":Q" & Target.Row + 1)
Cells(Target.Row, "G").Offset(1, 0).Formula = "=" & Cells(Target.Row, "G").Address(False, False) & "-" & Target.Address(False, False)
End If
End If
The part in red is the only addition.
I always use Q column for green rows, when I enter the quantity less than G column, I want to insert row below that cells
now it happened at the end and newG=Gup-Qup (G10=G9-Q9) but for example: I enter in Q4, so new row insert in below, and G5=G4-Q4, and all information in row 4 copy to row 5
Please keep in mind that the filtering you do after inserting the row is what pushes down the new row, the row is properly inserted below the target row.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
You can use Me.Unprotect at the beginning of the code, and Me.Protect at the end of code. (will be protected without a password)
But you have to make sure you have the target cells unlocked to allow changes in the cells that can trigger the code. If no cells are editable and unlocked, the code will not be triggered.
If you want to set a password:
Me.Unprotect Password:="MySecretPassword"
Me.Protect Password:="MySecretPassword"
1 Guest(s)