Using a variant for a workbook and worksheet

[288enzo]

Could someone please point me in the right direction. I'm trying to cycle through open workbooks, when particular ones are found I want to use wb1 and ws1 for a specific worksheet to be used later.

Honestly, I'm not even sure if I'm explaining that correctly.

Private Sub test()

    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
   
    Dim lr1 As Long, lr2 As Long
   

    For Each wb In Workbooks
        If wb.Name Like "*Registration*" Then
            wb1 = wb.Name
        End If
        If wb.Name Like "User_Report*" Then
            wb2 = wb.Name
        End If
    Next
   
    Set ws1 = wb1.Sheets("KPI Data")

    Set ws2 = wb2.Sheets("Sheet0")

    lr1 = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
    lr2 = ws2.Cells(ws1.Rows.Count, 1).End(xlUp).Row

End Sub

 

[Anthony47]

Not clear (to me) how you plan to use "later on" the variables; for sure, given that wb1 and wb2 are dimmed "as Workbook" (and not as String) the For Each /Next loop has to be modified:

    For Each wb In Workbooks
    'Debug.Print wb.Name
        If wb.Name Like "*Registration*" Then
            Set wb1 = wb
        ElseIf wb.Name Like "User_Report*" Then
            Set wb2 = wb
        End If
    Next

The instructions that follow are formally correct.

The variables in this macro "live" till End Sub; if you need their content be kept for subsequent use you have to declare them at module level, or even Public; see Declaring variables (VBA)

 

[288enzo]

Not clear (to me) how you plan to use "later on" the variables; for sure, given that wb1 and wb2 are dimmed "as Workbook" (and not as String) the For Each /Next loop has to be modified:

    For Each wb In Workbooks
    'Debug.Print wb.Name
        If wb.Name Like "*Registration*" Then
            Set wb1 = wb
        ElseIf wb.Name Like "User_Report*" Then
            Set wb2 = wb
        End If
    Next

The instructions that follow are formally correct.

The variables in this macro "live" till End Sub; if you need their content be kept for subsequent use you have to declare them at module level, or even Public; see Declaring variables (VBA)

Debug.Print wb1 and wb2 works perfectly in my original script.

Basically, if I can get debug.print lr1 and lr2 to work correctly I can figure out the rest. I guess that's my end game for help. If I have a bunch of workbooks open, how can I go about finding the last row for the specifed sheet for each specified workbook?

The last row for the workbook that is like *registration* sheets KPI Data and the last row for workbook that is like user_report* sheets sheet0?

I thought lr1 = wb1.ws1.cells(rows.count,1).end(xlup).row, or something similar would do the trick.

 

[288enzo]

Do I have to select the workbook first? I think that may be my issue.

I think that was the trick.

Private Sub test()

    For Each wb In Workbooks
        If wb.Name Like "*Registration*" Then
            wb1 = wb.Name
        End If
        If wb.Name Like "User_Report*" Then
            wb2 = wb.Name
        End If
    Next
   
    Windows(wb1).Activate
    Set ws1 = Sheets("KPI Data")
    lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
   
    Windows(wb2).Activate
    Set ws2 = Sheets("Sheet0")
    lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
   
    Debug.Print lr1
    Debug.Print lr2

End Sub

 

[Anthony47]

You don't need to activate a workbook (or a worksheet) to access its information

Did you understand how testing the code I suggested?
lr1 and lr2 should correctly contain the "last used row for column A", if the workbooks are open when you run the macro

 

[288enzo]

You don't need to activate a workbook (or a worksheet) to access its information

Did you understand how testing the code I suggested?
lr1 and lr2 should correctly contain the "last used row for column A", if the workbooks are open when you run the macro

I did not understand, sorry.

If I comment out each Activate, I get an error. I don't understand how to write it where I wouldn't need to activate the workbook.

 

[Anthony47]

Private Sub test()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
   
    Dim lr1 As Long, lr2 As Long
   

    For Each wb In Workbooks
        If wb.Name Like "*Registration*" Then
            Set wb1 = wb
        ElseIf wb.Name Like "User_Report*" Then
            Set wb2 = wb
        End If
    Next
    Set ws1 = wb1.Sheets("KPI Data")
    Set ws2 = wb2.Sheets("Sheet0")

    lr1 = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
    lr2 = ws2.Cells(ws1.Rows.Count, 1).End(xlUp).Row

Debug.Print "LR1 is: " & lr1
Debug.Print "LR2 is: " & lr2
End Sub

 

[288enzo]

Private Sub test()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
  
    Dim lr1 As Long, lr2 As Long
  

    For Each wb In Workbooks
        If wb.Name Like "*Registration*" Then
            Set wb1 = wb
        ElseIf wb.Name Like "User_Report*" Then
            Set wb2 = wb
        End If
    Next
    Set ws1 = wb1.Sheets("KPI Data")
    Set ws2 = wb2.Sheets("Sheet0")

    lr1 = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
    lr2 = ws2.Cells(ws1.Rows.Count, 1).End(xlUp).Row

Debug.Print "LR1 is: " & lr1
Debug.Print "LR2 is: " & lr2
End Sub

Got it, thank you!!!

 

[Anthony47]

Glad we passed the goal line...
If that resolve the problem then it'd be better to mark the discussion as Resoved; see the procedure: Mark as Solution

That code assumes that your *Registration* and User_Report* are open in the active application session.
You may test if the object has been set before going on; for example (second half of the macro):

'    previous instructions
    Next
   
    If Not wb1 Is Nothing Then
        Set ws1 = wb1.Sheets("KPI Data")
        lr1 = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
    End If
    If Not wb2 Is Nothing Then
        Set ws2 = wb2.Sheets("Sheet0")
        lr2 = ws2.Cells(ws1.Rows.Count, 1).End(xlUp).Row
    End If

Debug.Print "LR1 is: " & lr1
Debug.Print "LR2 is: " & lr2
End Sub