Error 91 using F5, but completes ok from pushbutton

[RogerE]

Hi,

After years of coding in other languages I have been asked to "automate" some Excel workbook actions, so this is my one of my first attempts using VBA.

One of them is copying the used cells in a couple of worksheets to a new workbook.

This VBA code works fine if run by clicking a button on the workbook, but if I press the F5 key from within the VBA environment it produces a runtime 91 error.

Function CreateCopy() As String
Dim ws As Worksheet
Dim iDataEnd As Integer
Dim iSumEnd As Integer
Dim sFilename As String
Dim sDir As String

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'Change text in below cell to where month's worksheet being stored after saving
sDir = Worksheets("Names").Range("I3").Value
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
 
      Application.ScreenUpdating = False
      iDataEnd = LastDataRow("A", 2, "Data")
      iSumEnd = LastDataRow("A", 2, "Summary")
      
      'Construct filename to save new workbook as
      sFilename = NewFileName()
      
      Worksheets(Array("Data", "Summary")).Copy
      ActiveWorkbook.Worksheets("Data").Rows(iDataEnd + 1 & ":" & Worksheets("Data").Rows.Count).Delete
      ActiveWorkbook.Worksheets("Summary").Rows(iSumEnd + 1 & ":" & Worksheets("Data").Rows.Count).Delete

      Set ws = ActiveWorkbook.Worksheet(1) '<---- Thought this might be problem line

      For Each ws In ActiveWorkbook.Worksheets
        ws.Cells.Copy
        ws.Cells.PasteSpecial xlPasteValues
      Next
      Application.CutCopyMode = False

      ActiveWorkbook.SaveAs Filename:=sDir & sFilename
      
      ActiveWorkbook.Save
      
      ActiveWorkbook.Close
      
      Application.ScreenUpdating = True
CreateCopy = sFilename
End Function

It looks like the problem is in the "For Each ws..." construct, so I thought it might be something to do with not having a "SET ws" line. However adding various forms of such a construct, e.g. Set ws = Activesheet (see code example above) does not stop the problem.

I know the code works ok outside of the development environment, but am trying to understand why this error occurs within it.

Thanks for your help

 

[Dryver14]

My first thought is that this should be a sub precedure not a function

 

[RogerE]

My first thought is that this should be a sub precedure not a function

It's called from the Button Click event:

Private Sub SaveBtn_Click()
' Monthly end procedure DOES NOT INCLUDE eMailing workbook
Dim i As Integer
Dim xCnt As Integer
Dim bReset As Boolean
Dim bIncomplete As Boolean

bReset = isAdmin(ComboBox1.Text)
  
  If bReset Then
  
    bIncomplete = IncompleteEntries()
    
    If bIncomplete Then
      ComboBox1.ListIndex = -1
      Exit Sub
    End If
    
    If MsgBox("Do you want to Save Data and clear for next month?" & vbCrLf & "Note: This option does not eMail the data", vbYesNo) = vbYes Then
      
      Call ClearEntryPage
            
      sFilename = CreateCopy()
   

     'Tidy up
     Call ClearMonthData
     MsgBox (sFilename & " has been saved as " & sDir & " " & sFilename & vbCrLf & "YOU WILL NEED TO SEND MANUALLY")

    End If
  Else
    MsgBox ("You are not authorised to use this function")
  End If
ComboBox1.ListIndex = -1

End Sub

 

[Dryver14]

Someone may correct me but a function cannot run by pressing F5

 

[RogerE]

Someone may correct me but a function cannot run by pressing F5

Let me explain a bit more.

There are 3 buttons on one of the worksheets, and the code above relates to just one of them. If I comment out the code that relates to the ws (the Dim line, the Set line and the For Each ws.....Next construct) and press either F5 or use the run to cursor it compiles ok.

If I leave those lines uncommented and use F5 or Run to cursor I get the 91 error. However if I just press the button, with those lines uncommented, it runs perfectly.

It is this I can't understand (possibly having come from languages where the compilers usually have a syntax test compile options has spoilt me)

Cheers

 

[Dryver14]

Set ws = ActiveWorkbook.Worksheet(1) '<---- Thought this might be problem line

Set ws = Activeworkbook.Worksheets(1)

 

[RogerE]

Set ws = ActiveWorkbook.Worksheet(1) '<---- Thought this might be problem line

Set ws = Activeworkbook.Worksheets(1)

That's sorted it - many thanks

Any idea why it worked when just clicked on the button, but bought up the error going through the debugger?

Cheers

 

[Dryver14]

I really haven't, sorry I didn't spot this straight away despite you pointing at it, I was just fixated on you trying to press F5 within a function!

 

[RogerE]

I really haven't, sorry I didn't spot this straight away despite you pointing at it, I was just fixated on you trying to press F5 within a function!

No problems - I'll just not get fixated with running the code from the debugger in future!

Thanks again for taking the time to answer!

 

[Norie]

RogerE

This might be a stupid question, but did you try Debug>CompileVBAProject before you tried running the code?