Code that normally works has stopped working!

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
Hi guys,

I have some code that has been working for months and suddenly it has stopped!

Code:
Sub Pricepack()
'Set Password
    Windows("pw.xls").Visible = True
    Sheets("PasswordSheet").Select
    Dim Password As Range
    Set Password = Range("B1")
    ActiveWindow.Visible = False
'Clear Row 3
    Rows("3:3").Select
    Selection.ClearContents
'Create filename
    a = Split([c1])
    b = 1 + UBound(a)
    Range([a3], Cells(3, b)) = a
'Select Pricepack Sheet
    Sheets("PRICEPACK").Select
'Resize Pricepack Rows to fit everything in
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 61
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 67
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 73
    ActiveWindow.ScrollRow = 74
    Rows("86:293").Select
    Selection.Rows.AutoFit
'Resize Pricepack Row 85 and Row 287 for company name
    Rows("85:85").Select
    Selection.RowHeight = 26.25
    Rows("287:287").Select
    Selection.RowHeight = 26.25
'Hide Rows That Have "HIDE THIS LINE" in column K
    Dim HideRange As Range
    Set HideRange = Range("K:K")
    For Each c In HideRange
    If c.Value = "HIDE THIS LINE" Then c.EntireRow.Hidden = True
    Next c
'Print Out Pricepack
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    "\\server1\hp LaserJet 1300 PS on Ne04:", Collate:=True
'Hide Unnecessary Sheets
    Sheets("DETAILS ENTRY").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("CUSTOMER STOCK PRICE LIST").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("IMPORT").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("CUSTOMER DISCOUNTS").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("GROUP DISCOUNTS").Select
    ActiveWindow.SelectedSheets.Visible = False
'Protect Sheet
    ActiveSheet.Protect Password
    ActiveSheet.EnableSelection = xlUnlockedCells
'Save File
    Dim new_filepath As String
    Dim new_filename As String
    new_filepath = ThisWorkbook.Path
    new_filename = new_filepath & Application.PathSeparator & Sheets("DETAILS ENTRY").Range("K5") & ".xls"
    Do
    If Chk(new_filename) Then
    new_filename = InputBox("This workbook already exists" & Chr(10) & "Please give new name", "TYPE FILENAME", Left(new_filename, Len(new_filename) - 4) & "2")
    If new_filename <> "" Then
    new_filename = new_filename & ".xls"
    Else
    MsgBox "You canceled", 48, "CANCELED"
    Exit Sub
    End If
    End If
    Loop While Chk(new_filename)
    ActiveWorkbook.SaveAs Filename:=new_filename
'Close Pricepack
    Application.DisplayAlerts = False
    ActiveWindow.Close
    Application.DisplayAlerts = True
'Reopen Electrical Pricepack Maker.xls
    Workbooks.Open Filename:="P:\Downloads\PRICEPACKS\After Price Increase (01-10-05)\ELECTRICAL PRICEPACK MAKER.xls"
End Sub

It highlights the "If c.Value = "HIDE THIS LINE" Then c.EntireRow.Hidden = True" line and says "Run-time error '13' : Type mismatch"

Does anyone know how to sort this for me?
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
Hi guys,

I've figured it out!

There was an error in the "HIDE THIS LINE" column and my formula was coming up as #REF!.

Thanks to anyone who started to look at this.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,116
Messages
5,570,273
Members
412,316
Latest member
JabirS
Top