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

rdoulaghsingh

Board Regular
Joined
Feb 14, 2021
Messages
83
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,163
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Feb 14, 2021
Messages
83
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
18,163
Office Version
  1. 2013
Platform
  1. Windows
Try this:
VBA Code:
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

Board Regular
Joined
Feb 14, 2021
Messages
83
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try this:
VBA Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
18,163
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Feb 14, 2021
Messages
83
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,163
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,253
Messages
5,745,681
Members
423,967
Latest member
malayaka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top