Hi all,
Very new to this! Thanks in advance for your help.
Scenario: I have a spreadsheet with 20 columns of info. A, B, and C are the only empty columns that are entered as they are confirmed/completed. The final step is to scan a barcode into column C next to the verified line. Once the barcode is scanned into C, that entire row is copied and pasted into a new sheet. Every row after that is pasted below the previous one on the new sheet (only one new sheet for all confirmed values that are copied and pasted). I found the following code that works if I use same value but because its a barcode and the value is different each time I need a code that will allow me to transfer any value present, nonblank value etc.
Sub MoveBasedOnValue()
Dim xRg As Range
Dim xCell As Range
Dim A As Long
Dim B As Long
Dim C As Long
A = Worksheets("Sheet1").UsedRange.Rows.Count
B = Worksheets("Sheet2").UsedRange.Rows.Count
If B = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 0 Then B = 0
End If
Set xRg = Worksheets("Sheet1").Range("C1:C" & A)
On Error Resume Next
Application.ScreenUpdating = False
For C = 1 To xRg.Count
If CStr(xRg(C).Value) = “Empty” Then '
xRg(C).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & B + 1)
xRg(C).EntireRow.Delete
If CStr(xRg(C).Value) = “Empty” Then
C = C - 1
End If
B = B + 1
End If
Next
Application.ScreenUpdating = True
Very new to this! Thanks in advance for your help.
Scenario: I have a spreadsheet with 20 columns of info. A, B, and C are the only empty columns that are entered as they are confirmed/completed. The final step is to scan a barcode into column C next to the verified line. Once the barcode is scanned into C, that entire row is copied and pasted into a new sheet. Every row after that is pasted below the previous one on the new sheet (only one new sheet for all confirmed values that are copied and pasted). I found the following code that works if I use same value but because its a barcode and the value is different each time I need a code that will allow me to transfer any value present, nonblank value etc.
Sub MoveBasedOnValue()
Dim xRg As Range
Dim xCell As Range
Dim A As Long
Dim B As Long
Dim C As Long
A = Worksheets("Sheet1").UsedRange.Rows.Count
B = Worksheets("Sheet2").UsedRange.Rows.Count
If B = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 0 Then B = 0
End If
Set xRg = Worksheets("Sheet1").Range("C1:C" & A)
On Error Resume Next
Application.ScreenUpdating = False
For C = 1 To xRg.Count
If CStr(xRg(C).Value) = “Empty” Then '
xRg(C).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & B + 1)
xRg(C).EntireRow.Delete
If CStr(xRg(C).Value) = “Empty” Then
C = C - 1
End If
B = B + 1
End If
Next
Application.ScreenUpdating = True