For things like this I prefer an onscreen indicator as to whether the current data has been transferred or not. So I added that in B10. As long as B10 doesn't say "YES", the macro will run. This is the formula in B10:
=IF(ISNUMBER(MATCH(B3, Log!B:B, 0)), "Yes", "No")
The macro:
Option Explicit
Sub TransferToLog()
Dim wsLOG As Worksheet, NR As Long, Rws As Long
Set wsLOG = Sheets("Log")
NR = wsLOG.Range("B" & Rows.Count).End(xlUp).Row + 1
With Sheets("Form")
If .Range("B10") = "Yes" Then
MsgBox "This data has already been transferred"
Exit Sub
End If
Rws = .Range("D6:D" & .Rows.Count).SpecialCells(xlConstants, 1).Rows.Count
wsLOG.Range("B" & NR).Resize(Rws, 6).Value = .Range("B3:G3").Value
wsLOG.Range("H" & NR).Resize(Rws, 2).Value = .Range("B6:C6").Value
wsLOG.Range("J" & NR).Resize(Rws, 3).Value = .Range("D6").Resize(Rws, 3).Value
End With
End Sub
Bookmarks