Object Required Error

[Demetris]

Hello, im a beginner in creating VBA macros to work around excel files easier.

When i run a specific procedure from Macros-> View Macros-> Edit (specific macro) the code runs perfectly. When i try to run it from Macros -> View Macros i get "Object Required" error

I would appriciate any help or tip

Code:
Sub TestingCellSelection()
Dim I_Customer As Integer, I_Import As Integer
Const Description_Col As Integer = 6
Const Style_Col As Integer = 7
Const Colour_Code_Col As Integer = 9
Const Colour_Col As Integer = 10
Const Supplier_Cost_Col As Integer = 12
Const CY_Retail_Col As Integer = 15
Const Size_Col As Integer = 11
Const QTY_Shipped_Col As Integer = 16
Const VAT_Col As Integer = 17
Const Customer_Row As Integer = 2
I_Import = 3
Sheets("Sheet1").Select
Dim LastRowImport As Long
With ActiveSheet
LastRowImport = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For I_Customer = 3 To LastRowImport Step 1

Dim Col_Customer As Integer

For Col_Customer = 8 To 32 Step 1

If Sheet1.Cells(I_Customer, Col_Customer) <> "" Then
Sheet2.Cells(I_Import, Description_Col) = Sheet1.Cells(I_Customer, 1)
Sheet2.Cells(I_Import, Style_Col) = Sheet1.Cells(I_Customer, 1)
Sheet2.Cells(I_Import, Colour_Code_Col) = Sheet1.Cells(I_Customer, 3)
Sheet2.Cells(I_Import, Colour_Col) = Sheet1.Cells(I_Customer, 3)
Sheet2.Cells(I_Import, Supplier_Cost_Col) = Sheet1.Cells(I_Customer, 4)
Sheet2.Cells(I_Import, 13) = 0
Sheet2.Cells(I_Import, CY_Retail_Col) = Sheet1.Cells(I_Customer, 7)
Sheet2.Cells(I_Import, Size_Col) = Sheet1.Cells(Customer_Row, Col_Customer)
Sheet2.Cells(I_Import, QTY_Shipped_Col) = Sheet1.Cells(I_Customer, Col_Customer)
Sheet2.Cells(I_Import, VAT_Col) = 15
I_Import = I_Import + 1
End If

Next Col_Customer

Next I_Customer

End Sub

 

[RoryA]

On which line does the error occur?

 

[Demetris]

Line 23

22. If Sheet1.Cells(I_Customer, Col_Customer) <> "" Then
23. Sheet2.Cells(I_Import, Description_Col) = Sheet1.Cells(I_Customer, 1)

 

[RoryA]

That would imply that the workbook that code is in does not have a sheet whose codename is Sheet2.

 

[Demetris]

I have another procedure which i execute first that creates Sheet2

There are 5 Procedures i run.

1. Sub AddNameNewSheet1()
2. ActiveSheet.Name = "Sheet1"
3. Sheets.Add Type:=xlWorksheet
4. ActiveSheet.Name = "Sheet2"
5. End Sub

 

[Jon von der Heyden]

You are calling it by the CodeName and you have renamed the sheet Name. These are two different properties. You can't change the CodeName with VBA, it's a read-only property.

You need to change your code to:

Sheets("Sheet2")

 

[Demetris]

Thank you very much. It worked perfectly

Much Appriciated for your replies and help

 

[RoryA]

You can change the code name with VBA - for example:

activeworkbook.VBProject.VBComponents(activesheet.codename).Name = "testing"

 

[Jon von der Heyden]

You can change the code name with VBA - for example:

activeworkbook.VBProject.VBComponents(activesheet.codename).Name = "testing"

Nice! I never realised this was do-able...