VBA Sheet Array, Referencing 2014 JAN - 2015 DEC as "YYYY MMM"

[srwilson87]

So I am very new to VBA, and I'm trying to piece together what I can on my own.
I have this code, used to remove data on a certain line, in reference to data being collected on a summary page.

I have employee's on row A7:B30 as Last Name/First Name. and their Data on each monthly tab there after.

Sub ER_AB14()'
' EmployeeRemoval_A14:B14 Macro
'


    'From here, on the Summary Page, it selects the name in the A:B column, Comment
    'in J column, Supervisor in K column, and Hire Date in the L Column, and then
    'clears the information.
    Sheet("Year-to-Date Summary").Select
        Range("A14:B14,J14:L14").Select
            Selection.ClearContents
    
    'On the summary page, Columns C:I, this data is fed by filling in dates for the
    'following monthly tabs
    
    
    'It now selects all data relevant to the employee on row 14, (row 13 for all the monthly tabs)
    'and clears out all the information being fed into columns C:I
    Sheets(Array("2014 JAN", "2014 FEB", "2014 MAR", "2014 APR", "2014 MAY", "2014 JUN", _
        "2014 JUL", "2014 AUG", "2014 SEP", "2014 OCT", "2014 NOV", "2014 DEC", "2015 JAN", _
        "2015 FEB", "2015 MAR", "2015 APR", "2015 MAY", "2015 JUN", "2015 JUL", "2015 AUG", _
        "2015 SEP", "2015 OCT", "2015 NOV", "2015 DEC")).Select
            Range("J13:AN13").Select
                Selection.ClearContents


    Sheets("Year-to-Date Summary").Select
    Range("A14").Select
End Sub

As it is set up now, this macro would only work until 2015 Dec.. and does not follow suite with the page as a whole, being able to track a rolling year.

I'm wanting to know how to reference the Tabs by "YYYY MMM" so it will function later on. Also, how to reference the Names, and then delete their data accordingly,
vs by manual selection, and a macro for each row.

Any assistance, or even pointing in a direction to learn what i need to, would be of immense help.

 

[AlphaFrog]

Try this to loop through all YYYY MMM sheets.

[color=darkblue]Sub[/color] ER_AB14() [color=green]'[/color]
[color=green]' EmployeeRemoval_A14:B14 Macro[/color]
[color=green]'[/color]
    [color=darkblue]Dim[/color] ws [color=darkblue]As[/color] Worksheet
    
    [color=green]'From here, on the Summary Page, it selects the name in the A:B column, Comment[/color]
    [color=green]'in J column, Supervisor in K column, and Hire Date in the L Column, and then[/color]
    [color=green]'clears the information.[/color]
    Sheets("Year-to-Date Summary").Range("A14:B14,J14:L14").ClearContents
    
    [color=green]'On the summary page, Columns C:I, this data is fed by filling in dates for the[/color]
    [color=green]'following monthly tabs[/color]
    
    [color=green]'It now selects all data relevant to the employee on row 14, (row 13 for all the monthly tabs)[/color]
    [color=green]'and clears out all the information being fed into columns C:I[/color]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] ws [color=darkblue]In[/color] Worksheets
        [color=darkblue]If[/color] ws.Name [color=darkblue]Like[/color] "#### ???" [color=darkblue]Then[/color] ws.Range("J13:AN13").ClearContents
    [color=darkblue]Next[/color] ws
    
    Application.Goto Sheets("Year-to-Date Summary").Range("A14")
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Also, how to reference the Names, and then delete their data accordingly,
vs by manual selection, and a macro for each row

I didn't understand what you want deleted. I get the names are in A7:B30, but then what? You want to delete rows 7 to 30?

 

[srwilson87]

Ok, to help better understand, here is my Worksheet.

TinyUpload.com - best file hosting solution, with no limits, totaly free

On the summary page is the name.. the subsequent tabs, Jan 2014-20?? DEC, there is data on those pages directly related to the individuals listed on the summary page.. that is what i'm removing.

Exp: Employee quits working for my company, their dates and points incurred during the time of employment is no longer needed. So I'm erasing it.

 

[srwilson87]

TinyUpload.com - best file hosting solution, with no limits, totaly free

Here is an updated version with your code for the month tabs, it did work, and I thank you for that.

So for reference, i have 25 of those macros, each for a specific row of information. How would I go about tying those into a drop down menu, that is tied to the name of the individual in A7:B31?