CODEWITHSUNDEEP
vbaexceldictionary
Sean Kelly
Sean Kelly

Reputation: 183

Simple VBA: Add and Name a Worksheet for Keys in Dictionary

This code is supposed to loop through the dictionary and, for each item/key, create a new worksheet and name it as the item/key. The items and the keys are exactly the same because this dictionary is an array of unique values created from a range of duplicates. I am receiving an error 424 Object Required at the last line:

Set objDictionary = CreateObject("Scripting.Dictionary")
Dim ws As Worksheet
For Each i In objDictionary.Items
    Set ws = Worksheets.Add(Worksheets(Worksheets.Count))
    ws.Name = objDictionary.Item(i).Value '<--- 424 OBJECT REQUIRED HAPPENS HERE
Next

Upvotes: 1

Views: 1083

Answers (1)

sourceCode
sourceCode

Reputation: 338

Get rid of .Value and make sure you declare the dictionary object and populate it

Dim ws As Worksheet, objDictionary As Object
Set objDictionary = CreateObject("Scripting.Dictionary")

For Each i In objDictionary.Items
    Set ws = Worksheets.Add(Worksheets(Worksheets.Count))
    ws.Name = objDictionary.Item(i)
Next

Upvotes: 4

Related Questions