Hello
I have multiple rows for a weekly update and I'd like to have 2 cells that the user would populate with a date from last week and the other would be a date the new update would be made. I want to look at column J and if it has a the date in the user entry cell (Previous week date) then copy the values in columns A:I and paste to the new inserted row below it in the same column. Column J in the new row should populate with the second date the user enters and copy cell in column L and paste it to column K in the new row. I can copy the entire row and get it to run but I don't know how to update the cells in J,K,L and add the dates by cell name only.
User data entry cell = 01/01/2020
User data entry cell = 01/07/2020
Click button
A1 = xyz
B1 = abc
C1 = 123
D1 = 789
E1 = aaa
F1 = bbb
g1 = ccc
H1 = ddd
I1=eee
J1=01/01/2020
K1 = 333
L1=222
M1=999
Would result in each row with a date of 01/01/2020 would have an inserted row below it and house the values below
A2 = xyz
B2 = abc
C2 = 123
D2 = 789
E2 = aaa
F2= bbb
G2 = ccc
H2 = ddd
I2 = eee
J2 = 01/07/2020
K2 = 222
L2 = blank
m2 = 999
Sub CopyPastelastvalue()
Dim Col As Variant
Dim BlankRows As Long
Dim Lastrow As Long
Dim R As Long
Dim StartRow As Long
Col = "J"
StartRow = 1
BlankRows = 1
Lastrow = Cells(Rows.Count, Col).End(xlUp).Row
Application.ScreenUpdating = False
With ActiveSheet
For R = Lastrow To StartRow + 1 Step -1
If .Cells(R, Col) = "11/17/2019" Then
.Cells(R, Col).EntireRow.copy
.Cells(R + 1, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With
Application.ScreenUpdating = True
End Sub
I have multiple rows for a weekly update and I'd like to have 2 cells that the user would populate with a date from last week and the other would be a date the new update would be made. I want to look at column J and if it has a the date in the user entry cell (Previous week date) then copy the values in columns A:I and paste to the new inserted row below it in the same column. Column J in the new row should populate with the second date the user enters and copy cell in column L and paste it to column K in the new row. I can copy the entire row and get it to run but I don't know how to update the cells in J,K,L and add the dates by cell name only.
User data entry cell = 01/01/2020
User data entry cell = 01/07/2020
Click button
A1 = xyz
B1 = abc
C1 = 123
D1 = 789
E1 = aaa
F1 = bbb
g1 = ccc
H1 = ddd
I1=eee
J1=01/01/2020
K1 = 333
L1=222
M1=999
Would result in each row with a date of 01/01/2020 would have an inserted row below it and house the values below
A2 = xyz
B2 = abc
C2 = 123
D2 = 789
E2 = aaa
F2= bbb
G2 = ccc
H2 = ddd
I2 = eee
J2 = 01/07/2020
K2 = 222
L2 = blank
m2 = 999
Sub CopyPastelastvalue()
Dim Col As Variant
Dim BlankRows As Long
Dim Lastrow As Long
Dim R As Long
Dim StartRow As Long
Col = "J"
StartRow = 1
BlankRows = 1
Lastrow = Cells(Rows.Count, Col).End(xlUp).Row
Application.ScreenUpdating = False
With ActiveSheet
For R = Lastrow To StartRow + 1 Step -1
If .Cells(R, Col) = "11/17/2019" Then
.Cells(R, Col).EntireRow.copy
.Cells(R + 1, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With
Application.ScreenUpdating = True
End Sub