Results 1 to 3 of 3

Thread: How to find the current month and previous month sheets in a workbook?

  1. #1

    Post How to find the current month and previous month sheets in a workbook?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,


    if the workbook sheet names are like this "Jan '12"....."Jun '12","Jul '12"..etc (All are in the same format)
    I am using this code to find out the current month and previous month sheets in my workbook
    Code:
    sub test()Dim CurrMonth As String, PrevMonth As String
    CurrMonth = Format$(Date, "mmm 'yy")
    PrevMonth = Format$(Date - Day(Date), "mmm 'yy")
    sheets(CurrMonth).select
    msgbox "current month",,currmonth
    sheets(prevmonth).select
    msgbox "previous month",,prevmonth
    end sub
    If the workbook sheet names are like this "Jan '12","Feb '12","Mar '12","April '12","May '12","June '12","Jul '12","Aug '12","Sept '12","Oct '12","Nov '12",
    "Dec '12"


    see these are not in the same format.


    Now how to find the current month and previous month sheets from this kind of workbook.


    Thanks in advance.


    Regards
    Kumar
    Last edited by ravikumar00008; 2012-07-24 at 04:26 PM.

  2. #2
    Hi,

    I got the solution like this
    Dim CurrMonth, PrevMonth, ws As Worksheet, x
    CurrMonth = Format$(Date, "mmm")
    PrevMonth = Format$(Date - Day(Date), "mmm")
    For Each ws In Sheets
    If ws.Name Like CurrMonth & "*" Then
    CurrMonth = ws.Name
    ElseIf ws.Name Like PrevMonth & "*" Then
    PrevMonth = ws.Name
    End If
    Next

    Regards
    Kumar

  3. #3
    Hi All,

    Additional thing what i want is,

    Right now i am able to identify the current month and previous month sheets.It is fine.
    I want another way also,Like
    I need to find all the previous months sheets from the current month along with the current month.

    ex:
    CurrMonth=July
    PrevMonths=June,May,April,March,Feb,Jan.

    Regards
    Kumar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •