Help to amend VBA to stop deleting a formula from cell

[dwpfreak]

Hi I have this VBA which copies and pastes data from one sheet into the next empty row on another worksheet.
The problem is, Cell E4 has a specific formula that works out the week value and it keeps being cleared when the macro is run.
Effectively I need cell A4 copied but not cleared.
Or I need to add code to put the (=AH8) basic formula back into the Cell "E4" on the "Record Data" Worksheet.
I would welcome some help

thank you

Sub PasteToNextEmptyRow()
Dim targetCell As Range
Set targetCell = Range("E4")
targetCell.Value = "=(P4)"
Range("a4:E4").Copy
Sheets("Income").Range("a" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("A4:E4").Select
Selection.ClearContents
Sheets("RECORD DATA").AutoFilter.ApplyFilter
Range("A6:A3000").Select
Selection.NumberFormat = "dd/mm/yyyy;@"
Range("C6:C3000").Select
Selection.NumberFormat = "$#,##0.00"
Range("B33").Select
ActiveSheet.Range("A4").Select

End Sub

 

[MARK858]

Last line in code before End Sub

Sheets("RECORD DATA").Range("E4").Formula2 = "=AH8"

Depending on your version (that you haven't stated or added to your profile) drop the 2 at the end of Formula if Excel objects

 

[dwpfreak]

Hi thank you, I have encountered a minor issue.

Issue: The data in Cell E4 from the record sheet is not moving over to Column E4 over on the Income sheet, but is moving the column V4 over to the Outgoings sheet.

I cannot see the difference in the code, (Both pasted below) that would explain why the code works for one and not the other.

I also have a problem when I run the sub for Outgoings it is deleting the formula in E4.

I am clearly doing something wrong but the info in Cell A4 is critical to later calculations so cannot progress.

I am sure I am missing something really daft here. But would appreciate some help.

Income Sub

Sub PasteToNextEmptyRow()
Dim targetCell As Range
Set targetCell = Range("E4")
targetCell.Value = "=(P4)"
Range("A4:E4").Copy
Sheets("Income").Range("a" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("A4:d4").Select
Selection.ClearContents
Sheets("RECORD DATA").AutoFilter.ApplyFilter
Range("A6:A3000").Select
Selection.NumberFormat = "dd/mm/yyyy;@"
Range("C6:C3000").Select
Selection.NumberFormat = "$#,##0.00"
Range("B33").Select
ActiveSheet.Range("A4").Select
Sheets("RECORD DATA").Range("E4").Formula2 = "=AH8"

End Sub

Outgoings Sub

Sub PasteToNextEmptyRow2()
Dim targetCell As Range
Set targetCell = Range("E4")
targetCell.Value = "=(P4)"
Range("r4:v4").Copy
Sheets("Outgoings").Range("a" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("r4:U4").Select
Selection.ClearContents
Sheets("RECORD DATA").AutoFilter.ApplyFilter
Range("A6:A3000").Select
Selection.NumberFormat = "dd/mm/yyyy;@"
Range("C6:C3000").Select
Selection.NumberFormat = "$#,##0.00"
Range("B33").Select
ActiveSheet.Range("r4").Select
Sheets("RECORD DATA").Range("v4").Formula2 = "=AH17"
End Sub

 

[MARK858]

We'll start from scratch, please answer the questions below

Which code is working and which is not?

Sub PasteToNextEmptyRow
What sheet are you on when the code runs?
Is targetCell.Value = "=(P4)" supposed to be a value or a formula?

Sub PasteToNextEmptyRow2
What sheet are you on when the code runs?
Is targetCell.Value = "=(P4)" supposed to be a value or a formula?

 

[dwpfreak]

emptyrow2 works as intended. Although deletes Cell A4 once run.

 

[MARK858]

Please answer all questions (and deletes A4 on what sheet?)

 

[dwpfreak]

Which code is working and which is not? PastetoNextEmptyrow2 works as intended, PasteToNextEmptyrow does not.

Sub PasteToNextEmptyRow
What sheet are you on when the code runs? "Record" sheet
Is targetCell.Value = "=(P4)" supposed to be a value or a formula? No idea sorry
I am not sure, the aim of the sub is to take the data from the record sheet from cells A4 to E4 and find then find the next empty row on Sheet "Income" and paste the content.
The problem I am encountering is the sub finds the next empty row fine, pastes the data from the Record sheet but it is not pasting the Week Number, that is left blank. The sub then clears the contents from the Record sheet so it is blank for another entry.

Sub PasteToNextEmptyRow2
What sheet are you on when the code runs?
Is targetCell.Value = "=(P4)" supposed to be a value or a formula?
This one runs fine, it takes the outgoings from R4 to V4, copies and finds next empty row on the Outgoings sheet and pastes all the data fine.

Answered as best I can, sorry I am a little novice this end.

 

[MARK858]

What sheet are you on when the code runs? "Record" sheet

Is that actually "RECORD DATA"?

sheet but it is not pasting the Week Number

What cell is Week Number being copied from?
What is in the cell Week Number is being copied from? a value or a formula?

 

[dwpfreak]

Hi first image is from the Record Data input sheet.
Week number is not copying from Cell E4 into the next empty row on sheet Income it will be copying into column E on the Income sheet, but the row number changes to the next free line..
The first three bits of data, the date, type and value copy over fine.
However, the record debit does copy the week number over.
Thank you for helping me,

 

[MARK858]

What is in Sheets("RECORD DATA").Range("P4") when you run the code?