Error message when opening another sheet with vba from an marcro.

[mattless1]

Hi,
can any one help me with this issue.

i have two workbooks one is my order form & the other is the master sheet where my orders are transferred to to save a master copy of all orders.
Every thing was working on on my laptop running Office 2016, but when running on my office Pc running Office2013 i get an error saying the master sheet is already open reopening it will disacard any changes you have made.

can anyone help here.

here is a copy of the code that opens the master sheet.

Sub CopyToMaster()
    Dim wMaster As Workbook, wMasterStr As String
    Dim shChristmas_Buchers_Orders As Worksheet
    Dim shMaster As Worksheet, shMasterStr As String
    Dim aCells() As String
    Dim sourceCells As String
    Dim nextRow As Range
    Dim i As Integer
    
    'Name of Master workbook
    wMasterStr = "Kilmac Master.xlsx"
    
    'Name of sheet in Master Workbook
    shMasterStr = "Sheet1"
    
    'Cells you want to copy from and paste into single row
    sourceCells = "C9,E8,J4,C7,H6,H7,H8,H9,I12,I13,I14,I15,I17,I18,I19,I20,D22,I22,I23,I24,C24,I26,I27,I28,I30,I31,I34,I35,I36,I39,I40,I41,I44,I45,I46,I47,I48,H42,I42,F51,G51,F52,G52,F53,G53,F54,G54,F55,G55,F56,F57,H57,I57"
    
    aCells = Split(sourceCells, ",")
    Set shInvoice = ActiveSheet
    For Each wMaster In Workbooks
        If wMaster.Name = wMasterStr Then
            Exit For
        End If
    Next
    
    If wMaster Is Nothing Then
        MsgBox "Please Contact Marco to open the master workbook first.", , wMasterStr & " not open"
        Exit Sub
    End If
    
    Set shMaster = wMaster.Sheets(shMasterStr)
    Set nextRow = shMaster.Cells(shMaster.Rows.Count, 1).End(xlUp)
    If nextRow.Value <> "" Then
        Set nextRow = nextRow.Offset(1, 0)
    End If
    nextRow.Value = nextRow.Row
    For i = 0 To UBound(aCells)
        shInvoice.Range(aCells(i)).Copy nextRow.Offset(0, i + 1)
        
    Next
    
    Dim wb As Workbook

Set wb = Application.Workbooks.Open("C:\Butchers Orders\Kilmac\Master Sheet\Kilmac Master.xlsx")
ActiveWorkbook.Save

wb.Close
    
End Sub

 

[DanteAmor]

Try this

Sub CopyToMaster()
    Dim wMaster As Workbook, wMasterStr As String
    Dim shChristmas_Buchers_Orders As Worksheet
    Dim shMaster As Worksheet, shMasterStr As String
    Dim aCells() As String
    Dim sourceCells As String
    Dim nextRow As Range
    Dim i As Integer
    
    'Name of Master workbook
    wMasterStr = "Kilmac Master.xlsx"
    
    'Name of sheet in Master Workbook
    shMasterStr = "Sheet1"
    
    'Cells you want to copy from and paste into single row
    sourceCells = "C9,E8,J4,C7,H6,H7,H8,H9,I12,I13,I14,I15,I17,I18,I19,I20,D22,I22,I23,I24,C24,I26,I27,I28,I30,I31,I34,I35,I36,I39,I40,I41,I44,I45,I46,I47,I48,H42,I42,F51,G51,F52,G52,F53,G53,F54,G54,F55,G55,F56,F57,H57,I57"
    
    aCells = Split(sourceCells, ",")
    Set shInvoice = ActiveSheet
    For Each wMaster In Workbooks
        If wMaster.Name = wMasterStr Then
            Exit For
        End If
    Next
    
    If wMaster Is Nothing Then
        MsgBox "Please Contact Marco to open the master workbook first.", , wMasterStr & " not open"
        Exit Sub
    End If
    
    Set shMaster = wMaster.Sheets(shMasterStr)
    Set nextRow = shMaster.Cells(shMaster.Rows.Count, 1).End(xlUp)
    If nextRow.Value <> "" Then
        Set nextRow = nextRow.Offset(1, 0)
    End If
    nextRow.Value = nextRow.Row
    For i = 0 To UBound(aCells)
        shInvoice.Range(aCells(i)).Copy nextRow.Offset(0, i + 1)
    Next
    
    wMaster.Close True
End Sub

 

[mattless1]

Hi Thanks,

But i still get the same error. even if the wMaster.close True.

 

[DanteAmor]

Do you have any other macro running?

I don't see in your macro where you are trying to open the book "Ballsbridge Master.xlsx"

 

[mattless1]

Hi,

Yes i have a few running. but they all work in office 2016.

Sub Masteropen()
Application.ScreenUpdating = False
Workbooks.Open ("C:\Butchers Orders\Kilmac\Master Sheet\Kilmac Master.xlsx")
ActiveWindow.WindowState = xlMinimized
ThisWorkbook.Activate
Application.ScreenUpdating = True
End Sub

thanks

 

[DanteAmor]

You can run the macro in a new book without executing any other code.
That is, isolate the code in an execution.

 

[mattless1]

thanks i will try that.