How to check if rows copied to another sheet by VBA Excel?

Question

I have workbook contains two sheets (Opened_Items & Closed_Items).
I have a macro to copy some rows from sheet (Opned_Items) to sheet (Closed_Items) based on certain condition.

Sometimes, because the workbook is shared, some rows are not copied.

How do I check by using VBA if data copied to sheet (Closed_Items)?

Note: Data in cells (C:E) is unique and can be used to check if data copied.

Sub Copy_to_Closed_Sheet()
   
Dim Close_Data As Range, Cell As Object

Set Close_Data = Worksheets("Opned_Items").Range("B3:B500")
For Each Cell In Close_Data
If Cell.value = "Close" Then
Cell.EntireRow.Copy

Worksheets("Closed_Items").Select
ActiveSheet.Range("A65536").End(xlUp).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone
End If
Next

End Sub

Answer 1

For your exact type of string, we can use re.findall here for a regex based approach:

inp = "123399383 (blahthing1)(blahthing2)(blahthing3)(blahthing4)"
output = ','.join(re.findall(r'\w+', inp))
print(output)  # 123399383,blahthing1,blahthing2,blahthing3,blahthing4

Answer 2

re.split() will let you split on the specific characters you have. This will allow non-word characters to exist in the strings:

import re

s = '123399383 (blah++thing1)(blaht-&^hing2)(blah  thing3)(blahthing4)'

# split on space or closing parenthesis      
# and opening parentheses   
re.split(r'[\s\)]\(', s)

# ['123399383', 'blah++thing1', 'blaht-&^hing2', 'blah  thing3', 'blahthing4)']

Answer 3

An alternative way to solve it using regex is;

original = "123399383(blahthing1)(blahthing2)(blahthing3)(blahthing4)"
new = re.sub("\W+", ",", s)[:-1]
print(new)