Copy named rows based on new sheet based on text comparison of two cells

[rdoulaghsingh]

Good day everyone! I hope everyone is doing well.

So here's what I'm trying to do...I need a VBS that will loop through a workbook of multiple worksheets and look at columns C and D for differences as long as the text does not match "Default settings" or "Configurations settings". If there are differences between the two columns, I want to:
1. copy the name of the worksheet tab starting at A3 on a new sheet called "Change List"
2. copy the row (only the first 4 columns) from the worksheet being compared and place the entries in B3, C3, D3 and E3 respectively on "Change List"
3. Recurse this action throughout the entire workbook until all differences are recorded from step 1 and 2 above in the "Change List" rows

Any help would be greatly appreciated. Thanks in advance!

 

[My Aswer Is This]

So search all sheets in the workbook.
And if column C and D do not match copy this row to a new sheet the script will make
But only copy data from column A B C D
And on new sheet put sheet name in column A of new sheet and Column A B C and D will be copied to Columns B C D E
Is all that correct?

 

[rdoulaghsingh]

So search all sheets in the workbook.
And if column C and D do not match copy this row to a new sheet the script will make
But only copy data from column A B C D
And on new sheet put sheet name in column A of new sheet and Column A B C and D will be copied to Columns B C D E
Is all that correct?

Thank you for your swift response. Yes - That is correct.

 

[My Aswer Is This]

Thank you for your swift response. Yes - That is correct.

Will get back with you with a script

 

[rdoulaghsingh]

Will get back with you with a script

Will get back with you with a script

Thank you!

 

[My Aswer Is This]

Try this:

Sub Copy_Rows()
'Modified  10/4/2021  10:45:10 PM  EDT
Application.ScreenUpdating = False
On Error GoTo M
Dim i As Long
Dim b As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Dim ans As String
ans = "Change List"
Sheets.Add(After:=Sheets(Sheets.Count)).Name = ans

For b = 1 To Sheets.Count - 1
    With Sheets(b)
        Lastrowa = Sheets(ans).Cells(Rows.Count, "B").End(xlUp).Row + 1
        Lastrow = Sheets(b).Cells(Rows.Count, "B").End(xlUp).Row
            For i = 1 To Lastrow
            If .Cells(i, 2).Value <> .Cells(i, 3).Value Then
                    Lastrowa = Sheets(ans).Cells(Rows.Count, "B").End(xlUp).Row + 1
                        .Cells(i, 1).Resize(, 5).Copy Sheets(ans).Cells(Lastrowa, 2)
                        Sheets(ans).Cells(Lastrowa, 1).Value = Sheets(b).Name
            End If
            Next
End With
Next


Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "We had a problem" & vbNewLine & "We may already have a sheet named " & vbNewLine & ans


End Sub

 

[rdoulaghsingh]

Try this:

Sub Copy_Rows()
'Modified  10/4/2021  10:45:10 PM  EDT
Application.ScreenUpdating = False
On Error GoTo M
Dim i As Long
Dim b As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Dim ans As String
ans = "Change List"
Sheets.Add(After:=Sheets(Sheets.Count)).Name = ans

For b = 1 To Sheets.Count - 1
    With Sheets(b)
        Lastrowa = Sheets(ans).Cells(Rows.Count, "B").End(xlUp).Row + 1
        Lastrow = Sheets(b).Cells(Rows.Count, "B").End(xlUp).Row
            For i = 1 To Lastrow
            If .Cells(i, 2).Value <> .Cells(i, 3).Value Then
                    Lastrowa = Sheets(ans).Cells(Rows.Count, "B").End(xlUp).Row + 1
                        .Cells(i, 1).Resize(, 5).Copy Sheets(ans).Cells(Lastrowa, 2)
                        Sheets(ans).Cells(Lastrowa, 1).Value = Sheets(b).Name
            End If
            Next
End With
Next


Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "We had a problem" & vbNewLine & "We may already have a sheet named " & vbNewLine & ans


End Sub

Good morning @My Aswer Is This. Thanks for providing the code. I tried using it, but after it creates the Change List sheet it goes to the message box and doesn't populate anything in the list. Also, I already have a worksheet which exists called "Change List", so I don't need to create a new sheet for that. I would just like to populate the existing sheet and skip all cells with the text "Default settings" or "Configurations settings" from the columns being compared and copied to "Change List".

Thanks again! Much appreciated!

 

[My Aswer Is This]

So remove the line of code where it creates the sheet named. Change List and then run the code.

And I'm not sure what this means:

I would just like to populate the existing sheet and skip all cells with the text "Default settings" or "Configurations settings" from the columns being compared.
Where did you mention that in your orginal post?

 

[rdoulaghsingh]

So remove the line of code where it creates the sheet named. Change List and then run the code.

And I'm not sure what this means:

I would just like to populate the existing sheet and skip all cells with the text "Default settings" or "Configurations settings" from the columns being compared.
Where did you mention that in your orginal post?

So that helped, but it's copying the rows where the values match in C and D next to each other. I only want it to copy the data in the row if there is a difference in text value between C and D side by side comparison.

To add a little more context, the two columns that are being compared have the text "Default settings" or "Configurations settings" in some cells as headers. I would like the script to look at columns C and D for differences as long as the text "Default settings" or "Configurations settings" is not the cell value of C and D respectively. Does that make sense?.

It's in the second line of the original post.

 

[My Aswer Is This]

So that helped, but it's copying the rows where the values match in C and D next to each other. I only want it to copy the data in the row if there is a difference in text value between C and D side by side comparison.

To add a little more context, the two columns that are being compared have the text "Default settings" or "Configurations settings" in some cells as headers. I would like the script to look at columns C and D for differences as long as the text "Default settings" or "Configurations settings" is not the cell value of C and D respectively. Does that make sense?.

It's in the second line of the original post.

I guess I miss read your original post.
I'm going to have to let someone else here on the forum help you.