0

I have a macro which runs well on my computer and few other peoples. However for some other users there getting an error message saying:

“Object doesn’t support this property or Method”. 

Option Explicit 

Sub Create_NewSheet_TopasteData() 
Dim test As Worksheet 
Sheets("Security Distribution").Copy After:=Sheets(Sheets.Count) 
Set test = ActiveSheet 
test.Name = Range("F2")     
End Sub
Community
  • 1
  • 1
James
  • 489
  • 4
  • 13
  • 33
  • The part of the code I believe that the issue is arising from is this: **bold** Option Explicit Sub Create_NewSheet_TopasteData() Dim test As Worksheet Sheets("Security Distribution").Copy After:=Sheets(Sheets.Count) Set test = ActiveSheet test.Name = Range("F2") End Sub **bold** I am copying a worksheet all together with its macro into different sheet. – James Mar 23 '15 at 12:54
  • @ katz please see above – James Mar 23 '15 at 12:57
  • I suggest you to read [this answer](http://stackoverflow.com/a/10717999/4519059), and change your code to a compatible code that prevent you from such errors ;). – shA.t Apr 25 '15 at 04:43
  • feel free to mark my answer as your solution if it helped you – Kᴀτᴢ Sep 12 '15 at 22:11

2 Answers2

1

Just replace

test.Name = test.Range("F2")

with

test.Name = test.Range("F2").Value

(tested with Excel 2007)

Kᴀτᴢ
  • 2,146
  • 6
  • 29
  • 57
  • Thanks for that, good piece of advice. This code works on my computer but if I try it on a different computer in the same team, it doesn’t work. I think it’s more down to Excel version or computer itself. I strongly believe that the code is ok. I am open opinions. @ katz – James Mar 23 '15 at 14:05
  • @James so are there different versions of excel installed on the other computers? – Kᴀτᴢ Mar 23 '15 at 14:16
  • @ katz Just discovered we have different versions of OLE’s – James Mar 23 '15 at 16:47
0

Your code is likely throwing an error on the line test.name = Range("F2"). Is there any data in cell F2? Note, since Range("F2") is not qualified, it is taking the information from the active sheet. So, make sure that the active sheet has an appropriate sheet name.

basodre
  • 5,720
  • 1
  • 15
  • 23