Information regarding the Run time error 9

Question

I am trying to copy the range of value from one sheet in excel to another. I have copied this formula from another part of my sheet that works however i cam coming up with the run time error 9.

Sub SaveJambStudEC()
'
' SaveCalcsJambEC Macro
'
Dim page As Integer
    page = Cells(4, "T").Value

    Range("A70:AN70").Select
    Selection.Copy

    Range("A71").Select
    ActiveCell.Offset(page, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("A1:O63").Select
    Selection.Copy
    Sheets("10.3 JambCalcs EC").Select
        Range("A1").Select
    ActiveCell.Offset((page - 1) * 63, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
   Selection.PasteSpecial Paste:=xlPasteFormats

    Sheets("9.3 Jamb Design EC").Select

    Range("T5").Select
    Selection.Copy
    Range("N9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("T31").Value = 0

    Call JambECsetDesignOptions
    Call CopyJambOptiValues

    Range("J9").Activate
End Sub

(1) Tell us which line errors and (2) read this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — SJR, Jul 04 '19 at 14:58
The Error is happening on: Sheets("10.3 JambCalcs EC").Select — Alex, Jul 04 '19 at 15:06
The error means that the active workbook does not contain a sheet named "10.3 JambCalcs EC". If in fact it does contain a sheet with that name, check for spelling errors and extra spaces. — Domenic, Jul 04 '19 at 15:46

Error 1004 · Answer 1 · 2019-07-04T18:49:07.997

1

using the below code you will check if there is a sheet with that name. if you dont receive any message box means that there is no sheet with such name

Option Explicit

Sub test()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

        If ws.Name = "10.3 JambCalcs EC" Then
            MsgBox "Sheet Appears"
            Exit Sub
        End If

    Next ws

End Sub

Note

'ThisWorkbook' refer to the workbook that the code included. If you want to clearly declare the workbook you could declare a variable 'Dim wb as Workbook' and then set the workbook 'Set wb=Workbooks("workbook name")'

edited Jul 04 '19 at 18:49

answered Jul 04 '19 at 16:05

Error 1004

7,877
3
23
46

Caveat: OP's code looks like it originated as a recorded macro, so that would be written in a standard procedural module, which means the `Cells` and `Range` and `ActiveCell` calls are all off whatever workbook is currently active, i.e. `ActiveWorkbook` - which may or may not be `ThisWorkbook`. – Mathieu Guindon Jul 04 '19 at 17:05
@Mathieu Guindon thanks for the clarification. I make a note in my answer trying to explain how to set a workbook variable. – Error 1004 Jul 04 '19 at 18:50

Information regarding the Run time error 9

1 Answers1