CODEWITHSUNDEEP
vbaexcel
Atul Vij
Atul Vij

Reputation: 241

Compare excels and copy rest of Information

I was working with optimisation of code and after review from man people asked me to use Option Explicit and define Variables for everything and shorten the code. Which i did to maximum possible But the below code copies data from another excel by asking path and copy some specific data in column V and W. Also there is formula which compare data and find exact rows and which need to be copy.

Now please help how should i optimise this code and give variables to it.

Or please provide code in which we can compare 2 excel for example: A2:E is same then it should copy H2:I

For Each ws In MainWB.Worksheets
    If ws.Name <> "Sap Data" And ws.Name <> "Automated BL Import" Then
        With MainWB.Worksheets(ws.Name)
            .Range("V1").Value = "When it will be Cleared or Action Taken/Required"
            .Range("W1").Value = "Backup Link"
            LastRow = MainWB.Worksheets(ws.Name).Range("B" & Rows.Count).End(xlUp).Row
            .Range("Q1:Q" & LastRow).Delete
        End With
    End If
Next ws
b = MsgBox("Do you want to update comments for current postings from previous month?" & vbCrLf & vbCrLf & "Note:- If are runing this macro for the 1st time plese choose option 'No'", _
           vbYesNo + vbQuestion, "Question")
If b = vbYes Then
    Filename = Application.GetOpenFilename(, , "Please select previous month BL comment file to update comments.", , False)
    If Filename <> "False" Then
        Workbooks.Open Filename, Format:=2
    End If
    updatesheet = ActiveWorkbook.Name
    For Each ws In MainWB.Sheets
        If ws.Name <> "Sap Data" And ws.Name <> "Automated BL Import" Then
            For Each ds In Workbooks(updatesheet).Sheets
                If ds.Name = ws.Name Then
                    LastRow = MainWB.Worksheets(ws.Name).Range("B" & Rows.Count).End(xlUp).Row
                    With MainWB.Worksheets(ws.Name)
                        .Range("T2:T" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-1],'[" & updatesheet & "]" & ws.Name & "'!R2C[-1]:R1048576C,2,0) = 0,"""",VLOOKUP(RC[-1],'[" & updatesheet & "]" & ws.Name & "'!R2C[-1]:R1048576C,2,0)),"""")"
                        .Range("U2:U" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-2],'[" & updatesheet & "]" & ws.Name & "'!R2C[-2]:R1048576C,3,0) = 0,"""",VLOOKUP(RC[-2],'[" & updatesheet & "]" & ws.Name & "'!R2C[-2]:R1048576C,3,0)),"""")"
                        .Range("V2:V" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-3],'[" & updatesheet & "]" & ws.Name & "'!R2C[-3]:R1048576C,4,0) = 0,"""",VLOOKUP(RC[-3],'[" & updatesheet & "]" & ws.Name & "'!R2C[-3]:R1048576C,4,0)),"""")"
                        .Range("W2:W" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-4],'[" & updatesheet & "]" & ws.Name & "'!R2C[-4]:R1048576C,5,0) = 0,"""",VLOOKUP(RC[-4],'[" & updatesheet & "]" & ws.Name & "'!R2C[-4]:R1048576C,5,0)),"""")"
                        .Range("X2:X" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-5],'[" & updatesheet & "]" & ws.Name & "'!R2C[-5]:R1048576C,6,0) = 0,"""",VLOOKUP(RC[-5],'[" & updatesheet & "]" & ws.Name & "'!R2C[-5]:R1048576C,6,0)),"""")"
                        .Range("T2:X" & LastRow).Value = MainWB.Worksheets(ws.Name).Range("T2:X" & LastRow).Value
                    End With

Upvotes: 2

Views: 85

Answers (1)

Davesexcel
Davesexcel

Reputation: 6982

Your bottom part is a mess, you are missing some end ifs, You are missing the dims for the variables

The first part of the code is below.

You need to explain what you are trying to do with the second part of the code.

    Sub Button1_Click()
    Dim wb As Workbook, ws As Worksheet
    Dim bk As Workbook, sh As Worksheet

    Set wb = Workbooks("ThisOne.xlsm")

    For Each ws In wb.Sheets
        If ws.Name <> "Sap Data" And ws.Name <> "Automated BL Import" Then
            With ws
                .Range("V1").Value = "When it will be Cleared or Action Taken/Required"
                .Range("W1").Value = "Backup Link"
                LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
                .Range("Q1:Q" & LastRow).Delete'?
            End With
        End If
    Next ws
    b = MsgBox("Do you want to update comments for current postings from previous month?" & vbCrLf & vbCrLf & "Note:- If are runing this macro for the 1st time plese choose option 'No'", _
               vbYesNo + vbQuestion, "Question")
    If b = vbYes Then
        Filename = Application.GetOpenFilename(, , "Please select previous month BL comment file to update comments.", , False)
        If Filename <> "False" Then
            Workbooks.Open Filename, Format:=2
        End If
    Else: Exit Sub
    End If
    Set bk = ActiveWorkbook

    ' updatesheet = ActiveWorkbook.Name'what is this for?
    For Each sh In bk.Sheets
        '        If sh.Name <> "Sap Data" And ws.Name <> "Automated BL Import" Then
        '            For Each ds In Workbooks(updatesheet).Sheets
        '                If ds.Name = ws.Name Then
        '                    LastRow = MainWB.Worksheets(ws.Name).Range("B" & Rows.Count).End(xlUp).Row
        '                    With MainWB.Worksheets(ws.Name)
        '                        .Range("T2:T" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-1],'[" & updatesheet & "]" & ws.Name & "'!R2C[-1]:R1048576C,2,0) = 0,"""",VLOOKUP(RC[-1],'[" & updatesheet & "]" & ws.Name & "'!R2C[-1]:R1048576C,2,0)),"""")"
        '                        .Range("U2:U" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-2],'[" & updatesheet & "]" & ws.Name & "'!R2C[-2]:R1048576C,3,0) = 0,"""",VLOOKUP(RC[-2],'[" & updatesheet & "]" & ws.Name & "'!R2C[-2]:R1048576C,3,0)),"""")"
        '                        .Range("V2:V" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-3],'[" & updatesheet & "]" & ws.Name & "'!R2C[-3]:R1048576C,4,0) = 0,"""",VLOOKUP(RC[-3],'[" & updatesheet & "]" & ws.Name & "'!R2C[-3]:R1048576C,4,0)),"""")"
        '                        .Range("W2:W" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-4],'[" & updatesheet & "]" & ws.Name & "'!R2C[-4]:R1048576C,5,0) = 0,"""",VLOOKUP(RC[-4],'[" & updatesheet & "]" & ws.Name & "'!R2C[-4]:R1048576C,5,0)),"""")"
        '                        .Range("X2:X" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-5],'[" & updatesheet & "]" & ws.Name & "'!R2C[-5]:R1048576C,6,0) = 0,"""",VLOOKUP(RC[-5],'[" & updatesheet & "]" & ws.Name & "'!R2C[-5]:R1048576C,6,0)),"""")"
        '                        .Range("T2:X" & LastRow).Value = MainWB.Worksheets(ws.Name).Range("T2:X" & LastRow).Value
        '                    End With
        '                End If
        '            Next ds
        '        End If
    Next sh

End Sub

Upvotes: 2

Related Questions