LiveLessons Excel VBA DVD - code error

[Giga_Me]

Hi - I have this CD but have a problem with the code presented in Lesson 31.

I get a compile error "Invalid use of property" on this line of code:

 RowCount = Lastrow - StartRow+1

The code works in the video and I don't think the RowCount variable is necessary...? Full code here:

Sub CreateWorkbooks()
    Dim WBO As Workbook ' original workbook
    Dim WBN As Workbook ' new workbook
    Dim WSO As Worksheet ' original worksheet
    Dim WSN As Worksheet ' new worksheet
    
    Set WBO = ActiveWorkbook
    Set WSO = ActiveSheet
    
    FinalRow = WSO.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    LastDept = Cells(2, 1)
    StartRow = 2
    
    For i = 2 To FinalRow
        ThisDept = WSO.Cells(i, 1)
        If ThisDept = LastDept Then
            ' do nothing
        Else
            ' We have a new department starting
            ' Copy all of the previous rows to a new workbook
            LastRow = i - 1
            RowCount = LastRow - StartRow + 1
            
            ' Create a new workbook.
            Set WBN = Workbooks.Add(Template:=xlWBATWorksheet)
            Set WSN = WBN.Worksheets(1)
            
            ' Set up the headings for the report
            WSN.Cells(1, 1).Value = "Budget Report"
            WSN.Cells(1, 1).Font.Size = 14
            
            WSN.Cells(2, 1).Value = LastDept & " - " & WSO.Cells(StartRow, 2)
            WSO.Range("C1:Q1").Copy Destination:=WSN.Cells(4, 1)
            
            ' copy all of the records for this department
            WSO.Range(WSO.Cells(StartRow, 3), WSO.Cells(LastRow, 17)).Copy Destination:=WSN.Cells(5, 1)
            
            FN = LastDept & ".xlsx"
            FP = WBO.Path & Application.PathSeparator & "Budget" & Application.PathSeparator
            
            WBN.SaveAs Filename:=FP & FN
            WBN.Close SaveChanges:=False
            
            LastDept = ThisDept
            StartRow = i
        End If
    Next i

End Sub

 

[Derek Brown]

I wonder if you have other code in the workbook (or add-ins) that is causing the problem.
Try declaring the variables:

Dim RowCount as Long
Dim Lastrow as Long
Dim StartRow as Long

 

[Giga_Me]

Derek I thought about declaring the values also. I copied the code from Bill's accompanying spreadsheet so there shouldn't have been extraneous code...I will review later. Also, because the RowCount variable only appears once and is not referenced again in the code, I'm wondering if it's even necessary...?

 

[Derek Brown]

Yes, you are right about the variable only appearing once and my guess is that it may have been code 'left over' from some diagnostic code.
I mentioned Add-Ins because I have experienced problems in the past where, in order to function as intended, variables etc. have been declared as global/public.
The code compiled OK for me.