Hi,
I have a little problem with some things,
I'm trying to insert a new row (anywere on the sheet), where a formula and text value is added automaticly based on the row above or below.
I have a formula in column J "=IF(OR(ISBLANK(NOW());ISBLANK(B9));"";NOW()-B9)", and a text (which is not always the same) in column A
So when I insert a new row I would like to have the new cell in colomn A and J automaticly filled with the text and formula from the row above.
Could this even be done with a worksheet.Change event?
I already have a code for the formula, but to add the text is more of a challenge for me.
Sub InsertRow()
Dim rw As Long, cl As Long
Selection.EntireRow.Insert
n = ActiveCell.Row
For cl = 1 To Columns.Count
If Cells(n - 1, cl).HasFormula Then
Cells(n - 1, cl).Copy Cells(n, cl)
End If
Next
Cells(n, 1).Select
End Sub
I also have have a macro that copies a row to another sheet, based on a value, but it doesnt copy it as a Value.
I tried different .Paste codes but that doesnt seem to work.
Could I get some help with that code too ?
The code I use :
Private Sub CommandButton1_Click()
Dim lr As Long, lr2 As Long, r As Long
lr = Sheets("AWG4").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("H1").Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
If Range("I" & r).Value = "Done" And Range("A" & r).Value = "E1" Then
Rows(r).Copy Destination:=Sheets("H1").Range("A" & lr2 + 1)
Rows(r).Delete
lr2 = Sheets("H1").Cells(Rows.Count, "A").End(xlUp).Row
End If
If Range("I" & r).Value = "done" And Range("A" & r).Value = "E1" Then
Rows(r).Copy Destination:=Sheets("H1").Range("A" & lr2 + 1)
Rows(r).Delete
lr2 = Sheets("H1").Cells(Rows.Count, "A").End(xlUp).Row
End If
Next r
End Sub
My main goal is to insert a new row with filled colomn A & J whenever I use the macro to copy a row to another sheet.
Hopefully somebody is willing to help me.
Thanks in advance
I have a little problem with some things,
I'm trying to insert a new row (anywere on the sheet), where a formula and text value is added automaticly based on the row above or below.
I have a formula in column J "=IF(OR(ISBLANK(NOW());ISBLANK(B9));"";NOW()-B9)", and a text (which is not always the same) in column A
So when I insert a new row I would like to have the new cell in colomn A and J automaticly filled with the text and formula from the row above.
Could this even be done with a worksheet.Change event?
I already have a code for the formula, but to add the text is more of a challenge for me.
Sub InsertRow()
Dim rw As Long, cl As Long
Selection.EntireRow.Insert
n = ActiveCell.Row
For cl = 1 To Columns.Count
If Cells(n - 1, cl).HasFormula Then
Cells(n - 1, cl).Copy Cells(n, cl)
End If
Next
Cells(n, 1).Select
End Sub
I also have have a macro that copies a row to another sheet, based on a value, but it doesnt copy it as a Value.
I tried different .Paste codes but that doesnt seem to work.
Could I get some help with that code too ?
The code I use :
Private Sub CommandButton1_Click()
Dim lr As Long, lr2 As Long, r As Long
lr = Sheets("AWG4").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("H1").Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
If Range("I" & r).Value = "Done" And Range("A" & r).Value = "E1" Then
Rows(r).Copy Destination:=Sheets("H1").Range("A" & lr2 + 1)
Rows(r).Delete
lr2 = Sheets("H1").Cells(Rows.Count, "A").End(xlUp).Row
End If
If Range("I" & r).Value = "done" And Range("A" & r).Value = "E1" Then
Rows(r).Copy Destination:=Sheets("H1").Range("A" & lr2 + 1)
Rows(r).Delete
lr2 = Sheets("H1").Cells(Rows.Count, "A").End(xlUp).Row
End If
Next r
End Sub
My main goal is to insert a new row with filled colomn A & J whenever I use the macro to copy a row to another sheet.
Hopefully somebody is willing to help me.
Thanks in advance