Excel Macro Help

[madmike72781]

Hi,

I am needing some help coming up with a Macro that can do several things.

First thing it should do is go down Column B, grab each new name in Column B , and create a new worksheet at the end and change the name of the Worksheet to the name that was grabbed in Column B. Then keep going through all the names in Column B, doing the same thing with each new name, until it reaches the end.

I am a little familiar with Excel Macros, but dont know enough to write a dynamic one such as this. Any help is greatly appreciated.

[Webtest]

Can the same name appear multiple times in Column B? If so, it complicates the situation a little because you'll have to save a list of each name that has been previously processed. Here is something very basic to play with:

Option Explicit
Sub Macro1()
    Dim aSheet As Worksheet
    Dim aCell As Range
    Dim aRange As Range
    
    'Create a handle for the master sheet
    Set aSheet = Sheets("Sheet1")
    'Create a handle for the source range
    Set aRange = aSheet.Range("B1:B10")  'Set this based on column format
    
    'Iterate through all the cells in the master list
    For Each aCell In aRange
        'Add a sheet for this name
        Sheets.Add
        'Name the sheet
        ActiveSheet.Name = aCell.Value
    Next aCell

    Set aSheet = Nothing    '<< edit - Clean House
    Set aRange = Nothing    '<< edit - Clean House
    Set aCell = Nothing     '<< edit - Clean House
End Sub

[madmike72781]

Hi Webtest, thanks for the reply. Just read through the code. Makes sense, thanks.

Sorry but unfortunatly yes, the same name will appear in column B. And once I have that part down, I am needing to copy the Rows of the same name from Column B to the corresponding sheet that was just created. Thanks again for the help.

[Webtest]

Sorry? Don't feel 'Sorry'! That's just the way it is, and it makes the problem a little more interesting!

I think I would use a Dictionary in this case ... it is just an easily searchable ordered list of Key/Value pairs of strings. The key would be the name of the added sheets, and the value would be the Address String of the bottom cell that is loaded on the sheet. That way when you find a duplicate name (Key Exists), then you just have to insert the new data immediately under the address in the dictionary, and then change the dictionary value.

In order to use a Dictionary, you need to be sure that "Microsoft Scripting Runtime" is included in your references. Just in case that is greek, in the Visual Basic window, in the Menu Bar select: Tools > References, and make sure Microsoft Scripting Runtime has a check mark in its selector box. Once you do that, you can look up "Dictionary" in the HelpHeap and it will give you the important functions ... Add, Exists, etc.

One caveat - make sure that each Name in column B is ABSOLUTELY the same in all instances. It takes almost no effort to turn Sam Smith Jr into Sam Smith Jr..

It is your turn now. Try some code and post it (be sure to start with code and end with /code - both in square brackets [] ). We'll see where to go from there.

I made a couple of subtle edits to the code in the previous message ... you might take a look at them.

[madmike72781]

Cool, thanks again Webtest. Ok I did some reading up, as much as I could find, on the Dictionary object for VB, I understand how it works, but not sure of the syntax. Well heres what I have so far. I think im going in the right direction, just not sure, I couldnt find many examples of someone actually using Exists.

Option Explicit
Sub Macro1()
    Dim aSheet As Worksheet
    Dim aCell As Range
    Dim aRange As Range
    Dim Dict As Dictionary
       
   
    'Create a handle for the master sheet
    Set aSheet = Sheets("Sheet1")
    'Create a handle for the source range
    Set aRange = aSheet.Range("B1:B10")  'Set this based on column format
    
    
    'Iterate through all the cells in the master list
    For Each aCell In aRange
        'Add a sheet for this name
        Sheets.Add
        'Name the sheet
        ActiveSheet.Name = aCell.Value
    
    
    ' Create a dictionary instance.
    Set Dict = New Dictionary
 
    Dict.Add aCell, rRange.EntireRow.Insert
    Dict.Exists aCell
    
    Next aCell
 
    Set aSheet = Nothing    '<< edit - Clean House
    Set aRange = Nothing    '<< edit - Clean House
    Set aCell = Nothing     '<< edit - Clean House
End Sub

Well let me know what you think, or if im even going in the right direction. Thanks again, and I noticed the edits, thanks.

[Webtest]

I understand how it works, but not sure of the syntax.

Hmmm ...

Sorry, but you have a LONG way to go. Study hard! Here is my final installment ... it is NOT optimized ... just a quick and dirty outline correcting some of your code:

Option Explicit
Sub Macro1()
    Dim srcSheet As Worksheet  'Source
    Dim dstSheet As Worksheet  'Destination
    Dim aCell As Range
    Dim aRange As Range
    Dim Dict As Dictionary
    Dim aRow As Long
    
    'Create a handle for the master sheet
    Set srcSheet = Sheets("Sheet1")
    'Create a handle for the source range
    Set aRange = srcSheet.Range("B1:B10")  'Set this based on column format
    
    'MOVED HERE - adr
    ' Create a dictionary instance.
    Set Dict = New Dictionary
    
    'Iterate through all the cells in the master list
    For Each aCell In aRange
        
        'MOVED THE FOLLOWING
        ''Add a sheet for this name
        'Sheets.Add
        ''Name the sheet
        'ActiveSheet.Name = aCell.Value
    
        ' Create a dictionary instance.
        ' NO - You do NOT want to create a new dictionary for every name!
        ' Note that I moved this line BEFORE the loop starts ...
        'Set Dict = New Dictionary

        'RELOCATED THE FOLLOWING:
        'Dict.Add aCell, rRange.EntireRow.Insert
        'Dict.Exists aCell
        
        'Has this name been processed already?
        If Not Dict.Exists(aCell.Value) Then
            'No, this is a new name.  Create a dictionary Key/Value entry
            Dict.Add aCell.Value, 1  '<<Starting ROW for data on Destination Sheet
            'Add a sheet for this name
            Sheets.Add
            'Name the sheet
            ActiveSheet.Name = aCell.Value
        End If
        
        'Set the destination sheet handle to the correct sheet
        Set dstSheet = Sheets(aCell.Value)
        'Fetch the current Row pointer for the Destination Sheet
        aRow = Dict.Item(aCell.Value)
        'Copy the current Source Row to the clipboard
        aCell.Row.Copy
        'Paste the clipboard to the destination Row from the dictionary
        dstSheet.Cells(aRow, "A").Paste
        'Advance the saved address for this destination sheet by one row
        Dict.Item(aCell.Value) = aRow + 1
        
    Next aCell

    Set aSheet = Nothing    '<< edit - Clean House
    Set aRange = Nothing    '<< edit - Clean House
    Set aCell = Nothing     '<< edit - Clean House
    Set Dict = Nothing      '<< edit - Clean House
End Sub

Good Luck and Good Learning!

[madmike72781]

Lol that bad huh

Well thanks for the help, and Ill keep at it.

[Webtest]

Hey, we all have to learn! Now you see some of the complexity of what you are setting out to do. You just have to decide how deeply you want to bury yourself in it.

Tune up the code to your taste so that it at least runs, and then post any problems you run into.

[Webtest]

Well, got a spare minute and I tested this and it runs for me. The "Paste" function is totally brain dameaged.

Option Explicit
Sub Macro1()
    Dim srcSheet As Worksheet  'Source
    Dim dstSheet As Worksheet  'Destination
    Dim aCell As Range
    Dim aRange As Range
    Dim Dict As Dictionary
    Dim aRow As Long
    
    'Create a handle for the master sheet
    Set srcSheet = Sheets("Sheet1")
    'Create a handle for the source range
    Set aRange = srcSheet.Range("B1:B10")  'Set this based on column format
    
    ' Create a dictionary instance.
    Set Dict = New Dictionary
    
    'Iterate through all the cells in the master list
    For Each aCell In aRange
        
        'Has this name been processed already?  If so, the key DOES exist.
        If Not Dict.Exists(aCell.Value) Then
            'No, this is a new name.  Create a dictionary Key/Value entry
            Dict.Add aCell.Value, 1  '<<Starting ROW for data on Destination Sheet
            'Add a sheet for this name
            Sheets.Add
            'Name the sheet
            ActiveSheet.Name = aCell.Value
        End If
        
        'Set the destination sheet handle to the correct sheet
        Set dstSheet = Sheets(aCell.Value)
        'Fetch the current Row pointer for the Destination Sheet
        aRow = Dict.Item(aCell.Value)
        'Copy the current Source Row to the clipboard
        aCell.EntireRow.Copy
        'Paste the clipboard to the destination Row from the dictionary
        ActiveSheet.Paste Destination:=dstSheet.Cells(aRow, "A")
        
        'Advance the saved address for this destination sheet by one row
        Dict.Item(aCell.Value) = aRow + 1
        
    Next aCell

    Set srcSheet = Nothing    'Clean House
    Set dstSheet = Nothing
    Set aRange = Nothing    'Clean House
    Set aCell = Nothing     'Clean House
    Set Dict = Nothing      'Clean House
End Sub

[madmike72781]

This is excellent!! Works like a charm!! I know I have a tendandacy to dive straight into the deep end, when im just learning to swim. Ive always learned better working backwards.

There are a few other extras im trying to get it to do, and this is making it 10 times better. Thanks again for all the help!!

[Webtest]

You've probably noticed in testing this code that it aborts to the debugger if the sheet you are trying to create already exists. You really should have an exception handler to process the case where the sheet name already exists.

Good Luck and Good Programming!