Hello
I am new at the forum and also new to VBA. However I have quite ambitious exercise to do.
I have workbook with two sheets: Sheet1 and Sheet2. Sheet1 is defined as table (this is important).
I want to copy cell B, D and F of the last row in Sheet1 once the cell in column G is filled up with string "ok" to the first empty row of Sheet2.
I tried to modify VBA code found here:
https://www.mrexcel.com/forum/excel...eet-into-another-worksheet-same-workbook.html
but with no success as there is a table in Sheet1 which happened to be incompatible with Insert instruction. It works if I remove table.
I copied these few lines of code below for your convinience. This procedure moves entire row between two sheets.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Sheet2.Range("rngDest")
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Sheet1.Range("rngTrigger")) Is Nothing Then
' Only trigger if the value entred is OK
If UCase(Target) = "OK" Then
'Ensure subsequent deletion of 'moved' row does NOT cause the Change Event to run again and get itself in a loop!
Application.EnableEvents = False
Target.EntireRow.Select
Selection.Cut
rngDest.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
Else
End If
End Sub
where rngTrigger is defined name in Sheet1 refering to column G:G and rngDest is defined name in Sheet2 refering to first empty row.
Can you help me please?
And sorry for my English
--
Piotr
I am new at the forum and also new to VBA. However I have quite ambitious exercise to do.
I have workbook with two sheets: Sheet1 and Sheet2. Sheet1 is defined as table (this is important).
I want to copy cell B, D and F of the last row in Sheet1 once the cell in column G is filled up with string "ok" to the first empty row of Sheet2.
I tried to modify VBA code found here:
https://www.mrexcel.com/forum/excel...eet-into-another-worksheet-same-workbook.html
but with no success as there is a table in Sheet1 which happened to be incompatible with Insert instruction. It works if I remove table.
I copied these few lines of code below for your convinience. This procedure moves entire row between two sheets.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Sheet2.Range("rngDest")
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Sheet1.Range("rngTrigger")) Is Nothing Then
' Only trigger if the value entred is OK
If UCase(Target) = "OK" Then
'Ensure subsequent deletion of 'moved' row does NOT cause the Change Event to run again and get itself in a loop!
Application.EnableEvents = False
Target.EntireRow.Select
Selection.Cut
rngDest.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
Else
End If
End Sub
where rngTrigger is defined name in Sheet1 refering to column G:G and rngDest is defined name in Sheet2 refering to first empty row.
Can you help me please?
And sorry for my English
--
Piotr
