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

srwilson87

New Member
Joined
Apr 24, 2015
Messages
5
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.

Code:
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
Try this to loop through all YYYY MMM sheets.

Code:
[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

New Member
Joined
Apr 24, 2015
Messages
5
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.
 

Forum statistics

Threads
1,081,523
Messages
5,359,263
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top