Code Randomly works - No Pattern to when it does not work. Help Please

[Poundland]

All,

I am baffled as to why the below code, which is part of a larger code stream, works on some occasions and not on others, it is very random.

I will take some time to explain...

The code extract below is part of a larger code project, which all works fine, and works consistently every day without fail.

The code below is doing the same thing 5 times, which is basically opening a blank workbook, copying a tab from another workbook to the new workbook and then saving the workbook..

Now here is the odd thing, when the code fails it will fail on the first copy function, when I skip past this, the code then successfully completes the other 4 copy functions, which basically mirror the first one.

When the blank workbook is opened, on occasion Excel cannot see the workbook despite the Debug,Print function knowing the workbook is there. The Copy function will copy over a create a blank worksheet into the Blank Workbook, but will not copy the required tab into the new workbook.

I have tried all sorts to get Excel to recognize the Blank Workbook, and sometimes it works and other times it doesn't. It is very strange...

I tried creating a new workbook also instead of opening a Blank workbook, but had the same issue, sometimes it worked and other times it didn't.

Can anybody give me any ideas on how I can get this to consistently work.

 Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
 ' New Code lins to rectify finding workbook issue
 Set wrkMu = GetWorkbook("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
    If Not wrkMu Is Nothing Then
        Debug.Print wrkMu.Name
    End If
' End of new code lines
wrkMu.Activate ' Code line added to try and rectify Workbook selection issue
Windows("Aged Stock - Blank.xlsx").Activate ' Code line added to try and rectify Workbook selection issue
wrkAged.Activate
Windows("Aged Stock - Blank.xlsx").Activate ' Code line added to try and rectify Workbook selection issue
With wrkAged.Sheets("Food Aged")
    .Copy Before:=Workbooks("Aged Stock - Blank.xlsx").Sheets(1)
End With
'Sheets("Food Aged").Copy Before:=Workbooks("Aged Stock - Blank.xlsx").Sheets( _
        1)
wrkMu.Activate
On Error Resume Next
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Food.xlsx", Password:="danielle"
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Food.xlsx", Password:="danielle"
On Error GoTo 0
wrkMu.Close False
' Non Food
Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
wrkAged.Activate
Sheets("Non Food Aged").Copy Before:=Workbooks("Aged Stock - Blank.xlsx").Sheets( _
        1)
wrkMu.Activate
On Error Resume Next
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Non Food.xlsx", Password:="danielle"
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Non Food.xlsx", Password:="danielle"
On Error GoTo 0
wrkMu.Close False
' GM
Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
 wrkAged.Activate
Sheets("GM Aged").Copy Before:=Workbooks("Aged Stock - Blank.xlsx").Sheets( _
        1)
 wrkMu.Activate
 On Error Resume Next
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - GM.xlsx", Password:="danielle"
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - GM.xlsx", Password:="danielle"
On Error GoTo 0
wrkMu.Close False
' Multiprice
Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
 wrkAged.Activate
Sheets("Multiprice Aged").Copy Before:=Workbooks("Aged Stock - Blank.xlsx").Sheets( _
        1)
 wrkMu.Activate
 On Error Resume Next
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Multiprice.xlsx", Password:="danielle"
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Multiprice.xlsx", Password:="danielle"
On Error GoTo 0
wrkMu.Close False
' All
Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
 wrkAged.Activate
Sheets("All Aged").Copy Before:=Workbooks("Aged Stock - Blank.xlsx").Sheets( _
        1)
        wrkMu.Activate
On Error Resume Next
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Manager.xlsx", Password:="danielle"
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Manager.xlsx", Password:="danielle"
On Error GoTo 0
wrkMu.Close False

[p45cal]

You could try this to replace ALL of your code above, whether it works or not is dependent on what code is called by GetWorkbook:

For Each shtnm In Array("Food", "Non Food", "GM", "Multiprice", "All")
  Set wrkMu = GetWorkbook("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
  wrkAged.Sheets(shtnm & " Aged").Copy Before:=wrkMu.Sheets(1)
  If shtnm = "All" Then shtnm = "Manager"
  wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - " & shtnm & ".xlsx", Password:="danielle"
  wrkMu.Close False
Next shtnm

Failing that try this:

Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
' New Code lins to rectify finding workbook issue
Set wrkMu = GetWorkbook("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
If Not wrkMu Is Nothing Then Debug.Print wrkMu.Name

wrkAged.Sheets("Food Aged").Copy Before:=wrkMu.Sheets(1)
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Food.xlsx", Password:="danielle"
wrkMu.Close False

' Non Food
Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
wrkAged.Sheets("Non Food Aged").Copy Before:=wrkMu.Sheets(1)
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Non Food.xlsx", Password:="danielle"
wrkMu.Close False
' GM
Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
wrkAged.Sheets("GM Aged").Copy Before:=wrkMu.Sheets(1)
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - GM.xlsx", Password:="danielle"
wrkMu.Close False
' Multiprice
Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
wrkAged.Sheets("Multiprice Aged").Copy Before:=wrkMu.Sheets(1)
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Multiprice.xlsx", Password:="danielle"
wrkMu.Close False
' All
Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
wrkAged.Sheets("All Aged").Copy Before:=wrkMu.Sheets(1)
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Manager.xlsx", Password:="danielle"
wrkMu.Close False

As to why it should fail sometimes on the first sheet, check that the name of the sheet is EXCATLY the same on the sheet tab and the code; no leading or trailing spaces. Take out any On Error Resume Next lines and tell us what error is thrown up.

I see you directly copy a sheet to a workbook Aged Stock - Blank.xlsx; is there anything special in this file before it's copied to (other sheets of importance?)? If not then it will be much simpler to create and save new workbooks from scratch rather than find a blank workbook.

[Poundland]

Thank you for taking the time to reply to me, I have placed the 1st Code section into my Code Stream, and it has run through on the first attempt ok.

The code is run on a schedule timed run each day at between 9am and 9:30am, the next auto run being tomorrow morning, I will let it run for a few days, and If I have any issues I will let you know.

Again thank you for your help.

[p45cal]

1. Could you reply to "I see you directly copy a sheet to a workbook Aged Stock - Blank.xlsx; is there anything special in this file before it's copied to (other sheets of importance?)? If not then it will be much simpler to create and save new workbooks from scratch rather than find a blank workbook. " of my last message?

2. Out of curiosity, what is the code behind GetWorkbook?

[Poundland]

1. I tried this first, creating a new workbook and saving it, but was experiencing exactly the same issue, Excel would create the workbook but then could not reference it so just errored on the worksheet copy function. I tried to add focus ie to activate the new workbook, and to select the new workbook, but again excel simply could not reference the new workbook. So I resorted to opening up a previously saved blank workbook, which worked better but still had the random focus issues...

2. I researched the issue I was having on the net and somebody suggested using the GetWorkbook command to act as another type of focus on the blank workbook, again hit and miss as to whether it is working or not as still experiencing the same issues.

[p45cal]

1. I tried this first, creating a new workbook and saving it, but was experiencing exactly the same issue, Excel would create the workbook but then could not reference it so just errored on the worksheet copy function. I tried to add focus ie to activate the new workbook, and to select the new workbook, but again excel simply could not reference the new workbook. So I resorted to opening up a previously saved blank workbook, which worked better but still had the random focus issues...

Then try:

SheetsToCopy = Array("Food", "Non Food", "GM", "Multiprice", "All")
For Each shtnm In SheetsToCopy
  wrkAged.Sheets(shtnm & " Aged").Copy
  Set wrkMu = ActiveWorkbook
  If shtnm = "All" Then shtnm = "Manager"
  wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - " & shtnm & ".xlsx", Password:="danielle"
  wrkMu.Close False
Next shtnm

2. I researched the issue I was having on the net and somebody suggested using the GetWorkbook command to act as another type of focus on the blank workbook, again hit and miss as to whether it is working or not as still experiencing the same issues.

As far as I know GetWorkbook is not a built-in excel function; when you're editing the code, if you put the editing cursor on the word GetWorkbook then on the keyboard press Shift + F2, it should take you to its code. Could you show it here?

[Poundland]

This is the code associated with GetWorkbook..

Public Function GetWorkbook(ByVal sFullName As String) As Workbook
    Dim sFile As String
    Dim wbReturn As Workbook
    sFile = Dir(sFullName)
    On Error Resume Next
        Set wbReturn = Workbooks(sFile)
        If wbReturn Is Nothing Then
            Set wbReturn = Workbooks.Open(sFullName)
        End If
    On Error GoTo 0
    Set GetWorkbook = wbReturn
End Function

I will try out the first code stream you posted and let you know how it works tomorrow morning. Running it through earlier it worked fine but I will see how it runs on the schedule in the morning.

[snb]

You'd better restrict to:

sub M_snb()
  For Each it In Array("Food", "Non Food", "GM", "Multiprice", "All")
    sheets(it).copy
    with activeworkbook
      .saveas "I:\H925 Trading Dashboard Reports\" & Format(date, "dd.mm.yyyy") & " Aged Stock - " & replace(it,"All","manager") & ".xlsx",51
      .close 0
    end with
  next
End Sub

NB. In saveas, fileformat is a required parameter since 2007

[Poundland]

P45crl,

Your first section of code you gave to me successfully ran on 2 consecutive days but failed on the third day, the code line it failed on is below, the message was unable to perform copy function.

wrkAged.Sheets(shtnm & " Aged").Copy Before:=wrkMu.Sheets(1)

I will try the second stream of code you provided and let you know how that goes..

[p45cal]

Yes, do that.
This one's not going to be obvious.
I'm wondering whether you have workbooks opened in the same instance of Excel, or perhaps there's some sheet or workbook protection in the source or destination workbooks - just speculating.
You could also try snb's code with one minor tweak, change his:

sheets(it).copy
to:

sheets(it & " Aged").copy

Otherwise, perhaps a TeamViewer session (Google it)?

[Poundland]

Thank you for the reply, I can clarify that there is no Workbook or Worksheet protection enabled on either the source or destination workbooks, however each workbook is opened and saved with a Password to Open the workbook.

Not sure what the Google TeamViewer is, or even if my IT department would have it enabled or blocked by our firewall..

I will try the second code stream and let you know how I get on.

[snb]

however each workbook is opened and saved with a Password to Open the workbook.

Ouch. Avoid this.