Cannot resolve subscript error excel vba

[drluke]

Have searched the forum, online etc but cannot work out why I am getting "Subscript out of Range" error in the below code.

The error is in this line

With Worksheets("Journal")

The rest of the with (which forms part of the rest of the code, but this is where the debugger shows the error)

Sub FuncAllocJnl()
Dim LastCol As Long, x As Long, i As Long
Dim LastRow As Long
Dim rng As Range, c As Range
Dim z As Variant
    Application.ScreenUpdating = False
    
    With Worksheets("Journal")
        .Rows("7:" & Rows.Count).ClearContents
        .Cells(4, 3).Value = Date
        .Cells(4, 5) = Format(Date, "yyyy")
        .Cells(4, 6) = Format(Date, "mm")
        .Cells(4, 6).NumberFormat = "mm"
        .Cells(4, 8).Value = "Functional Allocations" & " " & MonthName(Month(Date)) & " " & Year(Date)
    End With

Worksheet("Journal") exists in the workbook.
Any advise would be appreciated.

 

[Fluff]

With that error you do not have a sheet called "Journal" in the active workbook.
Do you have more than one workbook open?
Have you checked the sheet name for leading/trailing spaces?

 

[drluke]

I had another workbook open. The code works perfectly now that I have closed the other workbook. Does this mean that when the workbook with this code is open, no other workbook can be open? It's not uncommon for more than one workbook to be open at the same time.

 

[Fluff]

You can have any number of workbooks open, you just need to qualify the sheets.
So if the sheet is in the same workbook as the code you would use

With ThisWorkbook.Worksheets("Journal")

otherwise something like

With Workbooks("Book1.xlsm").Worksheets("Journal")

 

[drluke]

Great! Thank you.

 

[Fluff]

You're welcome