VBA Closing script getting stuck on wkb6

[SaraB802]

Hi, I have got the below script working all the way through to wkb6.Close SaveChanges:=False it closes all up to this point, but I can't work out why it is stopping at this one. Please help</SPAN>


Public Sub Update_Ref()</SPAN>
Dim lngCalc As XlCalculation</SPAN>
Dim wkb1 As Excel.Workbook, wkb2 As Excel.Workbook, wkb3 As Excel.Workbook, wkb4 As Excel.Workbook, wkb5 As Excel.Workbook, wkb6 As Excel.Workbook, wkb7 As Excel.Workbook, wkb8 As Excel.Workbook</SPAN>

With Application</SPAN>
.ScreenUpdating = False</SPAN>
lngCalc = .Calculation</SPAN>
.Calculation = xlManual</SPAN>
.EnableEvents = False</SPAN>
.EnableCancelKey = xlErrorHandler</SPAN>
End With</SPAN>

On Error GoTo proc_End</SPAN>

Set wkb1 = Workbooks.Open("https://celcombluenet.celcom.com.my/division/Sales/Shared%20Folder/Training%20Management%20Tool%20Central.xlsm")</SPAN>
Set wkb2 = ThisWorkbook</SPAN>
Set wkb3 = Workbooks.Open("https://celcombluenet.celcom.com.my/division/Sales/Shared%20Folder/Training%20Management%20Tool%20HQ.xlsm")</SPAN>
Set wkb4 = Workbooks.Open("https://celcombluenet.celcom.com.my/division/Sales/Shared%20Folder/Training%20Management%20Tool%20Eastern.xlsm")</SPAN>
Set wkb5 = Workbooks.Open("https://celcombluenet.celcom.com.my/division/Sales/Shared%20Folder/Training%20Management%20Tool%20Northern.xlsm")</SPAN>
Set wkb6 = Wrokbooks.Open("https://celcombluenet.celcom.com.my/division/Sales/Shared%20Folder/Training%20Management%20Tool%20Southern.xlsm")</SPAN>
Set wkb7 = Workbooks.Open("https://celcombluenet.celcom.com.my/division/Sales/Shared%20Folder/Training%20Management%20Tool%2Sabah.xlsm")</SPAN>
Set wkb8 = Workbooks.Open("https://celcombluenet.celcom.com.my/division/Sales/Shared%20Folder/Training%20Management%20Tool%20Sarawak.xlsm")</SPAN>

Call wkb2.Sheets("Ref").Range("A1:F2210").Copy</SPAN>
Call wkb1.Sheets("Ref").Range("A1").PasteSpecial(Paste:=xlValues)</SPAN>
Call wkb3.Sheets("Ref").Range("A1").PasteSpecial(Paste:=xlValues)</SPAN>
Call wkb4.Sheets("Ref").Range("A1").PasteSpecial(Paste:=xlValues)</SPAN>
Call wkb5.Sheets("Ref").Range("A1").PasteSpecial(Paste:=xlValues)</SPAN>
Call wkb6.Sheets("Ref").Range("A1").PasteSpecial(Paste:=xlValues)</SPAN>
Call wkb7.Sheets("Ref").Range("A1").PasteSpecial(Paste:=xlValues)</SPAN>
Call wkb8.Sheets("Ref").Range("A1").PasteSpecial(Paste:=xlValues)</SPAN>

Application.CutCopyMode = False</SPAN>

proc_End:</SPAN>
With Application</SPAN>
.ScreenUpdating = True</SPAN>
.Calculation = lngCalc</SPAN>
.EnableEvents = True</SPAN>
.EnableCancelKey = xlInterrupt</SPAN>
End With</SPAN>

wkb1.Close SaveChanges:=False</SPAN>
wkb3.Close SaveChanges:=False</SPAN>
wkb4.Close SaveChanges:=False</SPAN>
wkb5.Close SaveChanges:=False</SPAN>
wkb6.Close SaveChanges:=False</SPAN>
wkb7.Close SaveChanges:=False</SPAN>
wkb8.Close SaveChanges:=False</SPAN>
End Sub</SPAN>

 

[Andrew Poulsom]

One reason could be that wkb6 wasn't opened. You have:

On Error GoTo proc_End</SPAN>

 

[SaraB802]

Thanks Andrew, I also noticed the spelling mistake of workbook in the setting of wkb6, which was stopping it from opening. Then it got stuck on

wkb7.Close SaveChanges:=False

I have removed On Error GoTo proc_End and proc_End.

 

[SaraB802]

I forgot to add the error message still remains the same Run-time Error '91': Object variable or With Block area variable not set.

I have set up a test area on my laptop to continue to fix this offline, the first thing I have seen is it appears to run but, what i want is for it to copy from the master and paste into each of the separate workbooks, overwriting what is currently in them.

So far nothing is pasting into the workbooks.

I am sure it is simple, but thinking my brain has fried. The test macro text is below:

Sub Update()
Dim lngCalc As XlCalculation
Dim wkb1 As Excel.Workbook, wkb2 As Excel.Workbook, wkb3 As Excel.Workbook, wkb4 As Excel.Workbook, wkb5 As Excel.Workbook, wkb6 As Excel.Workbook, wkb7 As Excel.Workbook, wkb8 As Excel.Workbook, wkb9 As Excel.Workbook

With Application
.ScreenUpdating = False
lngCalc = .Calculation
.Calculation = xlManual
.EnableEvents = False
.EnableCancelKey = xlErrorHandler
End With

Set wkb1 = Workbooks.Open("C:\Users\hp\Desktop\Test\Book1.xlsx")
Set wkb2 = Workbooks.Open("C:\Users\hp\Desktop\Test\Book2.xlsx")
Set wkb3 = Workbooks.Open("C:\Users\hp\Desktop\Test\Book3.xlsx")
Set wkb4 = Workbooks.Open("C:\Users\hp\Desktop\Test\Book4.xlsx")
Set wkb5 = Workbooks.Open("C:\Users\hp\Desktop\Test\Book5.xlsx")
Set wkb6 = Workbooks.Open("C:\Users\hp\Desktop\Test\Book6.xlsx")
Set wkb7 = Workbooks.Open("C:\Users\hp\Desktop\Test\Book7.xlsx")
Set wkb8 = Workbooks.Open("C:\Users\hp\Desktop\Test\Book8.xlsx")
Set wkb9 = ThisWorkbook

Call wkb9.Sheets("wkb1").Range("A1:C2").Copy
Call wkb1.Sheets("wkb1").Range("A1").PasteSpecial(Paste:=xlValues)
Call wkb2.Sheets("wkb2").Range("A1").PasteSpecial(Paste:=xlValues)
Call wkb3.Sheets("wkb3").Range("A1").PasteSpecial(Paste:=xlValues)
Call wkb4.Sheets("wkb4").Range("A1").PasteSpecial(Paste:=xlValues)
Call wkb5.Sheets("wkb5").Range("A1").PasteSpecial(Paste:=xlValues)
Call wkb6.Sheets("wkb6").Range("A1").PasteSpecial(Paste:=xlValues)
Call wkb7.Sheets("wkb7").Range("A1").PasteSpecial(Paste:=xlValues)
Call wkb8.Sheets("wkb8").Range("A1").PasteSpecial(Paste:=xlValues)

Application.CutCopyMode = False


With Application
.ScreenUpdating = True
.Calculation = lngCalc
.EnableEvents = True
.EnableCancelKey = xlInterrupt
End With

wkb1.Close SaveChanges:=False
wkb2.Close SaveChanges:=False
wkb3.Close SaveChanges:=False
wkb4.Close SaveChanges:=False
wkb5.Close SaveChanges:=False
wkb6.Close SaveChanges:=False
wkb7.Close SaveChanges:=False
wkb8.Close SaveChanges:=False
End Sub

 

[Andrew Poulsom]

Why are you using Call?

 

[Norie]

Does this work?

Option Explicit

Sub UpdateWBs()
Dim lngCalc As XlCalculation
Dim wb As Workbook
Dim arrWBNames
Dim strPath As String
Dim I As Long

    With Application
        .ScreenUpdating = False
        lngCalc = .Calculation
        .Calculation = xlManual
        .EnableEvents = False
        .EnableCancelKey = xlErrorHandler
    End With

    ' change workbook names in array and the path as needed
    arrWBNames = Array("Book1", "Book2", "Book3", "Book4", "Book5", "Book6", "Book7", "Book8")
    strPath = "C:\Users\hp\Desktop\Test\"

    For I = LBound(arrWBNames) To UBound(arrWBNames)

        Set wb = Workbooks.Open(strPath & arrWBNames(I))

        ThisWorkbook.Sheets("Ref").Range("A1:F2210").Copy

        wb.Sheets("Ref").Range("A1").PasteSpecial Paste:=xlPasteValues

        wb.Close SaveChanges:=True
    Next I

    Application.CutCopyMode = False

    With Application
        .ScreenUpdating = True
        .Calculation = lngCalc
        .EnableEvents = True
        .EnableCancelKey = xlInterrupt
    End With

End Sub