How to combine Two VBA into one

[dharv3y78]

Hi MrExcel Form,

I have a issue where i have two excel VBA code that work fine independently one is a Private Sub and the other is a Public Sub, the issue is that when i try to add the Public Sub inside of Private Sub using a Call SubName it crashes excel or does not work correctly.

What i have is a spreadsheet in where if Cells in Column D equal True (by a Checkbox) then in column E it will clear the contents and i'm able to put in my own rate but if column D equals FALSE it will insert the rate from column F, now the issue that i have is that when i select the right dropdown in column B the rate shows in column F but won't put the data in column E until i have check the checkbox twice is which is why i have used a CopyPaste code that will run manually but i want it to all run fluently and automatically. Is there a way that this can happen without crashing the app.

The code i'm using are.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    On Error GoTo CleanUp
    Application.EnableEvents = False
    If Not Intersect(Target, Range("D2").EntireColumn) Is Nothing Then
        Set rng = Intersect(Target, Range("D2").EntireColumn)
        rng.Offset(0, 1).ClearContents
        If rng.Value = False Then
            rng.Offset(0, 1).Value = rng.Offset(0, 2).Value
        End If
    End If
CleanUp:
    Application.EnableEvents = True
End Sub

Public Sub CopyPaste()
    Dim Cell As Range
    Dim rngSource As Range
    Dim rngTarget As Range

    ' Define the source and target ranges
    Set rngSource = Range("F4:F11")
    Set rngTarget = Range("E4:E11")
    
    ' Loop through each cell in the source range
    For Each Cell In rngSource
        ' Check if the cell contains a number
        If IsNumeric(Cell.Value) Then
            ' Copy the value from column F to column E
            Cells(Cell.Row, "E").Value = Cells(Cell.Row, "F").Value
        End If
    Next
End Sub

Any help you can give or provide the correct code would be appreciated.
Thanks
DHarv3y78

 

[MikeVol]

Hello @dharv3y78. try the following untested one, maybe it will work

    ' ''''''''''''''''''''''''''''''''''''''''''''
    ' Here above is the rest of your code
    ' Call CopyPaste after handling column D logic
    Call CopyPaste

CleanUp:
    
    ' Ensure events are re-enabled
    Application.EnableEvents = True
End Sub

 

[dharv3y78]

Hi MikeVol,
Thanks for letting me know where to put the VBA code and while it does not crash the excel file but when column D equals True it will not clear the contents of column E for the relevent row that Column D equaled TRUE.

Thanks
DHarv3y78

 

[dharv3y78]

Hi, Is there another suggestion, as mention above when column D equals True it will not clear the contents of column E for the relevent row that Column D equaled TRUE.

Thanks
DHarv3y78