VBA Copy/Paste Glitch?

deLa

I'm having an odd issue.

I'm searching through a range searching for any cell that has a strikethrough, if a strikethrough is detected, then that entire row that contains data will get copied/pasted to another worksheet of the same workbook.

I'm also searching all cells above the cell that had the strikethrough, looking for the first cell with an interior.color = rgb(0,0,0) and once found it will place that data on the other worksheet as well.

Here's my code.

Private Sub CommandButton1_Click()

Dim ipWS As Worksheet, compWS As Worksheet
Dim compDest As Range, rrCell As Range
Dim alastRow As Long


Set ipWS = ThisWorkbook.Worksheets("In Processing")
Set compWS = ThisWorkbook.Worksheets("Completed")
Set compDest = compWS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

alastRow = ipWS.Cells(Rows.Count, "A").End(xlUp).Row


Dim rackRng As Range
Dim cellRng As Range

 
Application.FindFormat.Interior.Color = RGB(0, 0, 0)


For Each rrCell In ipWS.Range("A1:A" & alastRow).Cells

    If rrCell.Font.Strikethrough = True Then
    
            Set cellRng = ipWS.Range(rrCell, rrCell.End(xlToRight))
            cellRng.Copy compDest.Offset(0, 1)
            'Application.CutCopyMode = False
            
            Set rackRng = ipWS.Range(rrCell, rrCell.End(xlUp)).Find("*", , , , , xlPrevious, , , SearchFormat:=True)
            rackRng.Copy compDest
            'Application.CutCopyMode = False
            
            ipWS.Range(rrCell, rrCell.End(xlToRight)).EntireRow.Delete

            
            Set compDest = compDest.Offset(1, 0)
    End If
Next rrCell
    

    
            With compWS.Range("A:P")
                .Font.Strikethrough = False
                .ColumnWidth = 25
                .Font.Size = 14
                .WrapText = True
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlVAlignCenter
            End With

End Sub

the code works just fine if I have < 2 cells with a strikethrough. Once I have > 2 cells with a strikethrough it starts recognizing every other cell with a strikethrough and leaves those on the original worksheet.

If I hit the button again, then the ones that were skipped then move to the destination worksheet.

Here's some pics

enter image description here

skipped cells

destination worksheet

The 2nd picture is the result of the first time I click the button. It recognizes the first cell with strikethrough, then skips the next one, then grabs the 3rd one. But like I said before, if I press the button again, then the one that was skipped will then go to the worksheet. I have no idea why it's not grabbing every condition the first time

Am I doing something wrong here?

Application.cutcopymode = false is commented out because that didn't seem to work either.

I've tried about every single thing I know how. I've tried calling out both worksheets at every opportunity but that didn't work.

Can someone please help remove me from my madness.

deLa

Thanks for the tip in the right direction @Siddharth Rout.


Private Sub CommandButton1_Click()

Dim ipWS As Worksheet, compWS As Worksheet
Dim compDest As Range, rrCell As Range
Dim i As Integer
Dim alastRow As Long

Set ipWS = ThisWorkbook.Worksheets("In Processing")
Set compWS = ThisWorkbook.Worksheets("Completed")


alastRow = ipWS.Cells(Rows.Count, 1).End(xlUp).Row


Dim rackRng As Range
Dim cellRng As Range

Set compDest = compWS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

Application.FindFormat.Interior.Color = RGB(0, 0, 0)


For i = alastRow To 1 Step -1

            
            If Range(Cells(i, 1), Cells(i, 1)).Font.Strikethrough = True Then
           
                Set rackRng = ipWS.Range(Cells(i, 1), Cells(i, 1).End(xlUp)).Find("*", , , , , xlPrevious, , , SearchFormat:=True)
                    rackRng.Copy compDest
                        Application.CutCopyMode = False
                
                Range(Cells(i, 1), Cells(i, Columns.Count).End(xlToLeft)).Copy compDest.Offset(0, 1)
                    Application.CutCopyMode = False
                
                        Set compDest = compDest.Offset(1, 0)
                    
                    Range(Cells(i, 1), Cells(i, 1)).EntireRow.Delete
            End If

Next i
            With compWS.Range("A:P")
                .Font.Strikethrough = False
                .ColumnWidth = 25
                .Font.Size = 14
                .WrapText = True
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlVAlignCenter
            End With

End Sub

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related