VBA code to copy cells value into another table

[Camaalot]

I'm coming to you to ask for some help with this code :

Option Explicit

Dim lgn&


Dim plage1 As Range, plage2 As Range
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.EnableEvents = False
    lgn = Target.Row
    Set plage1 = Range("C" & lgn & ",E" & lgn & ",G" & lgn & ",I" & lgn & ",K" & lgn & ",M" & lgn)
    Set plage2 = Range("Q" & lgn & ",S" & lgn & ",U" & lgn & ",W" & lgn & ",Y" & lgn)
    plage2.Select
    If Not Intersect(Target, Union(plage1, plage2)) Is Nothing Then
        
        If Target.Column < 14 And UCase(Range("G" & lgn)) = UCase("Chequing to Savings transfer") Then
            Range("Q" & Target.Row) = Range("C" & Target.Row)
            Range("S" & Target.Row) = Range("G" & Target.Row)
            Range("W" & Target.Row) = Range("i" & Target.Row)
            
        ElseIf Target.Column > 14 And (UCase(Range("S" & lgn)) = UCase("Chequing to Savings transfer") _
                Or UCase(Range("S" & lgn)) = UCase("Savings to chequing transfer")) Then
            Range("C" & Target.Row) = Range("Q" & Target.Row)
            Range("G" & Target.Row) = Range("S" & Target.Row)
            Range("K" & Target.Row) = Range("U" & Target.Row)
        End If
    End If
    Application.EnableEvents = True
    
    
End Sub


Sub Evenement()
Application.EnableEvents = True
End Sub

Firstly : The code works but...

Secondly, the goal of this worksheet is to be able to copy the values of cells "C", "G" and "I" contained in Chequing account table
into the next available line of the Savings account table in columns "Q", "S" and "W".

Thirdly, Vice versa, I need the values of cells "Q", "S" and "U" of Savings account table into the next available line of the Chequing account table in columns "C", "G" and "K".

I attached the file for a better understanding.

Thank you very much for your help,

Camaalot

 

[NoSparks]

You can't post your file at this site. You will need to share it using something like Google Drive, One Drive, or Box .com

Make sure the file pertains to what you describe and are explaining, unlike at your cross post.

Maybe this will suit the verbiage...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
Dim chqLR As Long, savLR As Long

If Target.Row < 6 Or Target.Row > 102 Then Exit Sub
If Target.Column > 2 And Target.Column < 26 And Target.Column <> 15 And Target.Column Mod 2 = 1 Then

    Application.EnableEvents = False

    chqLR = Range("C" & 102).End(xlUp).Row
    savLR = Range("Q" & 102).End(xlUp).Row
    
    If Target.Column < 14 And UCase(Range("G" & Target.Row)) = UCase("Chequing to Savings transfer") Then
        Range("Q" & savLR + 1) = Range("C" & Target.Row)
        Range("S" & savLR + 1) = Range("G" & Target.Row)
        Range("W" & savLR + 1) = Range("i" & Target.Row)
    End If
    
    If Target.Column > 14 And (UCase(Range("S" & Target.Row)) = UCase("Chequing to Savings transfer") _
                Or UCase(Range("S" & Target.Row)) = UCase("Savings to chequing transfer")) Then
        Range("C" & chqLR + 1) = Range("Q" & Target.Row)
        Range("G" & chqLR + 1) = Range("S" & Target.Row)
        Range("K" & chqLR + 1) = Range("U" & Target.Row)
    End If
    
    Application.EnableEvents = True
    
End If
    
End Sub


Sub Evenement()
Application.EnableEvents = True
End Sub

 

[Camaalot]

Thank you for telling me.

You can download the file here.

TIA to anyone who can help me solving this problem.

Camaalot

 

[NoSparks]

Ignore post 2 and try this

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim chqLR As Long, savLR As Long

If Target.Row < 6 Or Target.Row > 102 Then Exit Sub
If Target.Column <> 7 And Target.Column <> 9 And Target.Column <> 19 And Target.Column <> 21 Then Exit Sub

Application.EnableEvents = False

chqLR = Range("C" & 102).End(xlUp).Row
savLR = Range("Q" & 102).End(xlUp).Row
    
If Target.Column = 7 Or Target.Column = 9 Then
    If UCase(Range("G" & Target.Row)) = UCase("Chequing to Savings transfer") _
       And Range("I" & Target.Row) <> "" Then
            Range("Q" & savLR + 1) = Range("C" & Target.Row)
            Range("S" & savLR + 1) = Range("G" & Target.Row)
            Range("W" & savLR + 1) = Range("i" & Target.Row)
    End If
End If

If Target.Column = 19 Or Target.Column = 21 Then
    If UCase(Range("S" & Target.Row)) = UCase("Savings to chequing transfer") _
       And Range("U" & Target.Row) <> "" Then
            Range("C" & chqLR + 1) = Range("Q" & Target.Row)
            Range("G" & chqLR + 1) = Range("S" & Target.Row)
            Range("K" & chqLR + 1) = Range("U" & Target.Row)
    End If
End If

Application.EnableEvents = True
End Sub

this uses the date column to figure the last row that was used.

 

[Camaalot]

HI NoSparks,

This is great. The only glitch is : if I insert values in the chequing table, they appear starting at row 27 of the Savings table !!!

Here's the link to the new file : 2018 Accounts test v2.2.xlsm

Is there a way you can fix this. It would be very much appreciated.

TIA,

Camaalot

 

[NoSparks]

That's because the date columns C and Q are being used to determine the last used row, and you have a formula in Q26.

 

[Camaalot]

[SOLVED] Re: VBA code to copy cells value into another table

NoSparks,

I didn't remember of that formula. So, got rid of it and it works perfect.

Thanks so much for your precious efforts in helping me and have a nice day.

Camaalot