Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

vba code correction

0
Sub WorkbookOpen()
    On Error Resume Next
    Application.ScreenUpdating = False

    aps = Application.PathSeparator
    wb = ThisWorkbook.Path
    wb0 = ThisWorkbook.Name
    Wb1 = "ap.xls"
    Wb2 = "PL.xlsx"

    Workbooks.Open (wb & aps & Wb1)
    Wb1 = ActiveWorkbook.Name
    If Err.Number <> 0 Then
        MsgBox Err.Description
        Exit Sub
    End If

    Workbooks.Open (wb & aps & Wb2)
    Wb2 = ActiveWorkbook.Name
    If Err.Number <> 0 Then
        MsgBox Err.Description
        Exit Sub
    End If

    ALL_SAME = True
    e = 2
    Do
        chk_e = Workbooks(Wb1).Sheets(1).Cells(e, "E")
        chk_y = Workbooks(Wb1).Sheets(1).Cells(e, "Y")
        a = WorksheetFunction.Match(chk_e, Workbooks(Wb2).Sheets(1).Range("A:A"), 0)
        If Err.Number = 0 Then
            With Workbooks(Wb2).Sheets(1)
                x = .Cells(a, .Columns.Count).End(xlToLeft).Column + 1
                If x < 3 Then x = 3
                .Cells(a, x) = chk_y
                If .Cells(a, x) <> .Cells(a, x - 1) Then ALL_SAME = False
            End With
            bg = xlNone
        Else
            bg = 6
            Err.Clear
        End If
        Workbooks(Wb1).Sheets(1).Cells(e, "E").Interior.ColorIndex = bg
        e = e + 1
    Loop Until Workbooks(Wb1).Sheets(1).Cells(e, "E") = Empty


End Sub


The code is perfect only i am unable to write the code to save and close all the workbooks (i have to save and close ap.xls and pl.xlsx  in the last stage of the code so plz help)

Answer
Discuss

Answers

0
Selected Answer

try

Workbooks(Wb1).close True
Workbooks(Wb2).close True
Discuss

Discussion

Thnx Alot Sir & Mam for giving ur precious time and great support to this post
Have a Great Day Sir/Mam
style36 (rep: 24) Aug 20, '19 at 11:16 am
Add to Discussion
0

Try this at the end of the macro:

Wb1.Close True
Wb2.Close True

Here is a reference: Close Excel Workbook using VBA Macros

Discuss

Discussion

Sir after putting this code  the file is not closed and the files have not saved 
style36 (rep: 24) Aug 18, '19 at 3:20 am
You have declared neither Wb1 nor Wb2. Judging by the variable names, they should be workbook objects, to wit "Workbook1" and "Workbook2", and Don's suggested code would be correct. However, by the use of the variables it seems that you mean them to be workbook names and that would make k1's suggestion to be applicable. The confusion is entirely of your making Style36. Declare all variables at the top of each procedure (or module) using the Dim statement. Make sure not to forget anyone by using the Option Explicit statement at the top of each module. Option Explicit alerts you to errors and omissions. If you would have enough coding experience not to need help then you would know that refusing the advantages of Option Explicit is a waste of time, as is here demonstrated.
Variatus (rep: 4889) Aug 19, '19 at 2:16 am
Ah, I read the macro too fast and just assumed the workbooks were set to those variables and didn't catch that they were just string variables :P
don (rep: 1989) Aug 20, '19 at 2:31 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login