Problem Setting A Worksheet

[Ark68]

Please consider this code ...

On workbook Open ...

Option Explicit
    Public wb_main As Workbook
    Public ws_anraw As Worksheet
    Public ws_anwork As Worksheet
    Public rpt_date As Date

Private Sub Workbook_Open()
Stop
    Dim wscnt As Integer
    Dim i As Integer
    Set wb_main = Workbooks("Data.xlsm")
    wscnt = ActiveWorkbook.Worksheets.Count
    For i = wscnt To 1 Step -1
        If Worksheets(i).Name = "Sheet1" Then
            Application.DisplayAlerts = False
            wb_main.Worksheets(i).Delete
            Application.DisplayAlerts = True
        End If
    Next i
    Set ws_anwork = wb_main.Worksheets("ACTIVE_WORKING")
    
    transfer_raw
End Sub

I am getting a "Variable not defined" error with the object highlighted in the code below.

Sub transfer_raw()
    Dim wbnum As Long
    Dim wb As Workbook
    Dim ws As Worksheet
    wbnum = 0
    For Each wb In Workbooks
        wbnum = wbnum + 1
    Next wb
    Stop
    If wbnum < 2 Then
        MsgBox "There are no raw ActiveNet workbooks open."
    Else
        MsgBox "There are " & wbnum & " open worksheets."
        For Each wb In Workbooks
            If wb.Name Like "active_report*" Then
                Workbooks(wb.Name).Worksheets("Sheet1").Copy Before:=wb_main.Worksheets("PERMITS")
                Stop
                Set ws_anraw = wb_main.Worksheets("Sheet1")
                cleanData
            End If
        Next wb
    End If
End Sub

I have declared wb_main as public in the workbook declarations, and set it in the workbook open module. But it appears to not have carried over.

 

[Micron]

If both procedures are in the same wb, my guess would be that Excel variable scope does not allow that. Maybe if the variable was in a sheet module it would be visible to other sheets, but you're creating it at the workbook module level so perhaps that's the problem. You could test this in the immediate window, or just have the open event pass the workbook or workbook name to the transfer sub.

EDIT - I believe it's because a sheet and a workbook are class modules and scope is restricted to an instance of a class. You should not have this issue if the variable is declared in a standard module. So you should be ok if the wb open event calls a sub in a standard module, and that module creates the variable.

 

[Ark68]

Hi Micron,
My apologies for the tardiness in acknowledging your reply.
You indeed had the solution. I had to put the declarations and set them in a module. (I hope I got the terminology right.)

 

[Micron]

Glad I could help & thanks for the recognition!