VBA macro to compare two sheets and paste the differences in a new sheet.

[rkfuture]

I want to compare column A of worksheet 1 and worksheet 2, If there is an extra value in worksheet 2, I insert a blank row in worksheet 1 at the exact same place as worksheet 2. After the worksheets columns A match, I need to subtract all the cells in the columns generally starting from column B to however long the range goes. ( I need a way to find the total range of the sheet with numerical values excluding the headers)

The first part of the code ( to compare), takes two sheets called sheet 1 and sheet 2, can someone tell me how Instead of hardcoding the sheet names as sheet 1 and sheet 2, I can select sheets having keywords (before , after) That is: sheet 1 = any sheet that has "before" in its name and sheet 2 = any sheet that has "after" in its name.

Then I need to subtract sheet2 - sheet1, and put the difference in a new created sheet called variance.

View attachment 74289 This is how a typical sheet would look like ( The numbers here are cost budgets)

Sub Compare()
Dim lastRowE As Integer
Dim lastRowF As Integer
Dim lastRowM As Integer
Dim foundTrue As Boolean

Application.ScreenUpdating = False

LastRow1 = Sheets("sheet1").Cells(Sheets("sheet1").Rows.Count, "A").End(xlUp).Row
lastRow2 = Sheets("sheet2").Cells(Sheets("sheet2").Rows.Count, "A").End(xlUp).Row

For i = 1 To lastRow2
foundTrue = False
For j = 1 To LastRow1

    If Sheets("sheet2").Cells(i, 1).Value = Sheets("sheet1").Cells(j, 1).Value Then
        foundTrue = True
        Exit For
    End If

Next j

If Not foundTrue Then
    Sheets("Sheet1").Rows(i).EntireRow.Insert Shift:=xlShiftDown
    Sheets("sheet2").Cells(i).Copy Destination:= _
    Sheets("Sheet1").Cells(i, 1)
End If

Next i


Application.ScreenUpdating = True
End Sub

 

[Joe4]

Welcome to the Board!

can someone tell me how Instead of hardcoding the sheet names as sheet 1 and sheet 2, I can select sheets having keywords (before , after) That is: sheet 1 = any sheet that has "before" in its name and sheet 2 = any sheet that has "after" in its name.

You can use worksheet variables, and set them like this:

    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    
'   Loop through all worksheets in workbook
    For Each ws In ActiveWorkbook.Worksheets
'       Look for "Before" worksheet
        If InStr(LCase(ws.Name), "before") > 0 Then
            Set ws1 = ws
        End If
'       Look for "After" worksheet
        If InStr(LCase(ws.Name), "after") > 0 Then
            Set ws2 = ws
        End If
    Next ws

 

[rkfuture]

Welcome to the Board!


You can use worksheet variables, and set them like this:

    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
   
'   Loop through all worksheets in workbook
    For Each ws In ActiveWorkbook.Worksheets
'       Look for "Before" worksheet
        If InStr(LCase(ws.Name), "before") > 0 Then
            Set ws1 = ws
        End If
'       Look for "After" worksheet
        If InStr(LCase(ws.Name), "after") > 0 Then
            Set ws2 = ws
        End If
    Next ws

Hello,

Thanks a lot for the reply and the code!
But instead of temporary storing the sheet name in the variable, this asks me to rename the sheet to ws1 and ws2 with a windows pop up, every time i run the code.

Sheets("Variance1").Range("A1").CurrentRegion.Offset(1, 1).Formula = "='Resource-After'!B2-'Resource-Before'!B2"
Sheets("Variance2").Range("A1").CurrentRegion.Offset(1, 1).Formula = "='Expense-After'!B2-'Expense-Before'!B2"

I am using this formula in my VBA code, and as you can see I am referencing directly the sheet name, I am trying to find a way where macro identifies the sheets itself and put them in the formula respectively.
I tried using your macro and changing the formula to Formula = "='ws2'!B2-'ws1'!B2, but as I said it requires me to change the name of the sheet with a pop up manually.

Regards,

 

[Joe4]

I tried using your macro and changing the formula to Formula = "='ws2'!B2-'ws1'!B2, but as I said it requires me to change the name of the sheet with a pop up manually.

If you are trying to capture the worksheet name to put into formulas, you can still use the code I provided, but then you can reference the sheet name using "ws1.Name".

So instead of having this:

Sheets("Variance1").Range("A1").CurrentRegion.Offset(1, 1).Formula = "='Resource-After'!B2-'Resource-Before'!B2"

you would have:

Sheets("Variance1").Range("A1").CurrentRegion.Offset(1, 1).Formula = "='" & ws2.Name & "'!B2-'" & ws1.Name & "'!B2"

 

[Alex Blakenburg]

Assuming that your sheet names are as consistently named as you are indicating (ie a common base name such as "Resource-" with either before or after appended to it) then this might work for you.
I have not tried to change your formula apart from filling in the correct sheet names.

Sub CreateVarianceSheet()

    Dim shtBefore As Worksheet, shtAfter As Worksheet, shtVar As Worksheet
    Dim shtCurr As Worksheet
    Dim BaseOfShtName As String
    
    For Each shtCurr In Worksheets
        ' Identify Sheet with the word Before in it
        If InStr(1, shtCurr.Name, "Before", vbTextCompare) Then
            BaseOfShtName = Replace(shtCurr.Name, "Before", "")
            ' Look for matching sheet with the word After in it
            If Evaluate("=ISREF('" & BaseOfShtName & "After" & "'!A1)") Then
                Set shtBefore = shtCurr
                Set shtAfter = Worksheets(BaseOfShtName & "After")
                ' If both the before and after sheet exist then unless it already exists
                ' create a new matching sheet with the word Variance in it
                If Evaluate("=ISREF('" & BaseOfShtName & "Variance" & "'!A1)") Then
                    Set shtVar = Worksheets(BaseOfShtName & "Variance")
                    shtVar.UsedRange.Clear
                Else
                   ' Worksheets.Add.Name = BaseOfShtName & "Variance"
                    Worksheets.Add
                    Set shtVar = ActiveSheet
                    shtVar.Name = BaseOfShtName & "Variance"
                End If
                ' Formula as provided by OP > replace the tilda "~" with the common Base Name
                shtVar.Range("A1").CurrentRegion.Offset(1, 1).Formula = Replace("='~After'!B2-'~Before'!B2", "~", BaseOfShtName)
            End If
        
        End If
    
    Next shtCurr

End Sub