Copy Formula from Cell 200 of that column into deleted Cell - VB

[eli_m]

Hi - This site has helped me so much I thought I'd sign up and ask my own question and hopefully help others

My question:
What would be the VB code for when I delete data from a cell it then automatically copies the formula that's in cell 200 of that row into the newly deleted cell?

eg.

1. Delete data in cell A1
2. Formula from A200 gets put into cell A1

I also want it to work for all my columns which is A to AR.

Another example:

1. Delete data from F68
2. Formula from F200 populates into F68

Any help would be greatly appreciated!

Thank you in advance

 

[Peter_SSs]

Good news!

 

[eli_m]

Hi,
This has been working great but when I paste into a cell that already has a value that ISN'T from cell 200 I get these errors:

Is there a way to fix this?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.CountLarge / Rows.Count = Int(Target.CountLarge / Rows.Count) Then Exit Sub 'Exit code if whole columns are edited

' Copy from Line 200 into deleted cells
  Dim Changed As Range, c As Range
 
  Set Changed = Intersect(Target, Columns("A:AS"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If IsEmpty(c.Value) Then Cells(200, c.Column).Copy Destination:=c
    Next c
    Application.EnableEvents = True
  End If

' Ignore Errors with Worksheet Clicks
Dim r As Range: Set r = Range("A2:AS200")
Dim cel As Range

For Each cel In r
  With cel
    .Errors(8).Ignore = True 'Data Validation Error
    .Errors(9).Ignore = True 'Inconsistent Error
    .Errors(6).Ignore = True 'Lock Error
  End With
Next cel

End Sub

 

[Peter_SSs]

when I paste into a cell that already has a value that ISN'T from cell 200 I get these errors:

I have not been able to reproduce that. Can you explain an example of ..
- which cell did you copy?
- what was in that cell?
- which cell did you paste into?
- what was in that cell before you pasted?
- what is in row 200 of the column that you are pasting into?

 

[eli_m]

I have not been able to reproduce that. Can you explain an example of ..
- which cell did you copy?
- what was in that cell?
- which cell did you paste into?
- what was in that cell before you pasted?
- what is in row 200 of the column that you are pasting into?

- which cell did you copy? - Copied text from an email
- what was in that cell? - Cell had a 07/07/2022 in it
- which cell did you paste into? Q53
- what was in that cell before you pasted? Cell had a 07/07/2022 in it
- what is in row 200 of the column that you are pasting into?

=IF([@Manufacturer]="Australia","Pre-Book Loan Set","")

Even I now can't replicate it. I've had it happen numerous times when pasting something in. I'll keep an eye on it and report back if it happens again and hopefully they I can replicate it!

 

[Peter_SSs]

What range on the sheet does the whole table containing the 'Manufacturer' column occupy?
What worksheet column is 'Manufacturer' in?

 

[eli_m]

What range on the sheet does the whole table containing the 'Manufacturer' column occupy?
What worksheet column is 'Manufacturer' in?

Column L - L1:L200

 

[Peter_SSs]

The whole table cannot be just column L as the table must also include (at least) the column that the formula is going into.

What range on the sheet does the whole table containing the 'Manufacturer' column occupy?

 

[eli_m]

The whole table cannot be just column L as the table must also include (at least) the column that the formula is going into.

A2:AS200

 

[Peter_SSs]

Thanks.
So we will just have to wait to see what happens in relation to this:

I'll keep an eye on it and report back if it happens again and hopefully they I can replicate it!

 

[eli_m]

Thanks.
So we will just have to wait to see what happens in relation to this:

I am to replicate the error another way:

Column AH has "DHL: xxxxxxxxx":

When I change that to just numbers eg. 326041959578 I get:

When I click Debug I get:

Full code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = False Then
Application.Calculate
End If
End Sub

' Ignore Errors when Worksheet Activated
Private Sub Worksheet_Activate()

Dim r As Range: Set r = Range("A2:AS200")
Dim cel As Range

For Each cel In r
  With cel
    .Errors(8).Ignore = True 'Data Validation Error
    .Errors(9).Ignore = True 'Inconsistent Error
    .Errors(6).Ignore = True 'Lock Error
  End With
Next cel

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.CountLarge / Rows.Count = Int(Target.CountLarge / Rows.Count) Then Exit Sub 'Exit code if whole columns are edited

' Copy from Line 200 into deleted cells
  Dim Changed As Range, c As Range
 
  Set Changed = Intersect(Target, Columns("A:AS"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If IsEmpty(c.Value) Then Cells(200, c.Column).Copy Destination:=c
    Next c
    Application.EnableEvents = True
  End If

' Ignore Errors with Worksheet Clicks
Dim r As Range: Set r = Range("A2:AS200")
Dim cel As Range

For Each cel In r
  With cel
    .Errors(8).Ignore = True 'Data Validation Error
    .Errors(9).Ignore = True 'Inconsistent Error
    .Errors(6).Ignore = True 'Lock Error
  End With
Next cel

End Sub
' Auto Refresh Pivot Tables
Private Sub Worksheet_Deactivate()
  ThisWorkbook.RefreshAll

On Error Resume Next

End Sub