Can you guys help me fixing the error?

Hot Topic

Programatically Changing Excel Default Delimiter in VB.NET
VBA excel - problem with having clause in sql with adodb.connection/recordset
Application.FileSearch in Excel 2007
.Net and DoD/NSA acceptance
Problem with Defined names
enabling controls at runtime
Sharepoint 2007 Site Template
Problem with VBA script
list object
After_Save Event???

Microsoft ISV

Change Data Source in Word Document
Help with interface?
Defining a range according to the number of responses
Working with Outlook Public Folders from Excel
Collections of control - evaluating the name of a control that has been "pressed"
Displaying modeless userform while other workbooks are opened and closed
Installing VBA6.0 on XP
how to delete all names in workbook?
close excel when finish
Displaying modeless userform while other workbooks are opened and closed

Can you guys help me fixing the error?

I'm currently working for engineering company and it is my first time using visual basic woth Excel application. My job is to find the error and fix it, but i just dont know how. I have tried few things but it seems not working. Can you guys help it would be greatly appreciated. Thanks.

The program contains macros and is a excel application.

Here's the error message I get:

Run-time error '1004':

Select method of Worksheet class failed

And here's a section of code (Hi-lighted line seems to contain error):

Sub CheckSummerWinter()
'Check to see if Summer/Winter selection changed
'Remove protection
Passwrd = "ESGtrcCv" & Trim(Right(Worksheets("EnerSpectrum Group").Range("Version"), 4))
Worksheets("Avoided Load Profile").Unprotect (Passwrd)

If Worksheets("EnerSpectrum Group").OptionButton1.Value = True Then ' Summer Peaking
Sheets("Avoided Load Profile").Select
' Avoided Distribution Capacity Costs
Range("AW7").Select
If Worksheets("NPV TRC").OptionButton4 Then 'Direct Input
ActiveCell.Formula = "=AV7*(L7/L$6*M7)"
Else ' OEB Table
ActiveCell.Formula = "=AV7*M7"
End If
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Worksheets("HiddenData").Range("PriorPresentYear") = "S"
Range("C7").Select
Else 'Must be Winter Peaking
Sheets("Avoided Load Profile").Select
' Avoided Distribution Capacity Costs
Range("AW7").Select
'Worksheets("Avoided Load Profile").Range("AW7").Select ' Avoided Distribution Capacity Costs
If Worksheets("NPV TRC").OptionButton4 Then 'Direct Input
ActiveCell.Formula = "=AV7*(C7/C$6*D7)"
Else ' OEB Table
ActiveCell.Formula = "=AV7*D7"
End If
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Worksheets("HiddenData").Range("PriorPresentYear") = "W"
Range("C7").Select
End If
' Reset Generation Capacity formulas based on NPV OptionButtion
Sheets("Avoided Load Profile").Select
Range("AS7").Select
If Worksheets("NPV TRC").OptionButton4 Then 'Direct Input
ActiveCell.Formula = "=AR7*(L7/L$6*M7)"
Else ' OEB Table
ActiveCell.Formula = "=AR7*M7"
End If
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Worksheets("HiddenData").Range("PriorPresentYear") = "W"
Range("C7").Select
' Reset Transmission Capacity formulas based on NPV OptionButtion
Range("AU7").Select
If Worksheets("NPV TRC").OptionButton4 Then 'Direct Input
ActiveCell.Formula = "=AT7*(L7/L$6*M7)"
Else ' OEB Table
ActiveCell.Formula = "=AT7*M7"
End If
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Worksheets("HiddenData").Range("PriorPresentYear") = "W"
Range("C7").Select
' Sheets("EnerSpectrum Group").Select

'Restore Protection
Worksheets("Avoided Load Profile").Protect Password:=Passwrd, DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub 'CheckSummerWinter

Answer this question

Can you guys help me fixing the error?

  • leolima

    First of all, I'd recommend using Sheets("Avoided Load Profile").Activate instead of .Select and secondly you don't have to activate each sheet and select the range before you copy or change the formulas. That only slows your actions down... for example, instead of

    Sheets("Avoided Load Profile").Select
    Range("AW7").Select
    If Worksheets("NPV TRC").OptionButton4 Then
    ActiveCell.Formula = "=AV7*(C7/C$6*D7)"
    Else ' OEB Table
    ActiveCell.Formula = "=AV7*D7"
    End If

    You could use
    With Sheets("Avoided Load Profile").Range("AW7")
    if Worksheets("NPV TRC").OptionButton4 Then
    ActiveCell.Formula = "=AV7*(C7/C$6*D7)"
    Else ' OEB Table
    ActiveCell.Formula = "=AV7*D7"
    End If
    End With

    and so on... or you could set the range into a range object and use that:
    dim rSelRange as Range
    ...
    set rSelRange = Sheets("Avoided Load Profile").Range("AW7")

    It's much more efficient to work with ranges than always selecting and using Selection... it also avoids errors like you seem to be experiencing. That error could come up when you try to select a sheet that's hidden, so check if the sheet is hidden and then unhide it (when you don't select ranges or sheets, but work with ranges like in my example, you don't get errors like these).

    Hope this helps solve your problem!:)

  • Matthew Cosner

    Thanks for your reply. However this is my first time using this Program so I dont know how to unhide the worksheets. Can you help me with this too Thank you so much.

  • Gbobbage

    Have you checked for any hidden worksheets
    You can check again by unhiding the worksheets and then selecting a particular range on them
    If you are directly selecting a range on a hidden worksheet....that might create a problem

    Try n let me know if your query was answered
    Thanks

    amzzz
  • Can you guys help me fixing the error?
Answer this question