Pulling current months sheet to a new workbook based on the current month

Question

Ok I currently have one data base that tracks data on a month by month basis so there is a new tab or worksheet for each month. I have a second workbook I am using to processes this data. I have the current code below and it works great the only issue is I will have to maunaully go in and change the sheet it is pulling each month. "Sheets("APRIL 15).select". My question is; is there an equation that I could use to pull the sheet for the current month?

Current Code

   Range("A1").Select

'
    Workbooks.Open Filename:= _
        "X:\GLOBLOPS\TRADE_PROCESSING\Procedures\Corporate Action\Full Call\Full Calls Tracker.xls"

    Sheets("APRIL 15").Select
    ActiveWindow.SmallScroll Down:=-27
    Columns("A:I").Select
    Selection.Copy
    Windows("Copy of CorpAct_freeDeliver 3.13.15.xls").Activate
    ActiveWindow.SmallScroll Down:=-12
    ActiveSheet.Paste
    Windows("Full Calls Tracker.xls").Activate
    ActiveWindow.Close
   Windows("Copy of CorpAct_freeDeliver 3.13.15").Activate
   Sheets("Call Tracker").Select

score 1 · Answer 1 · edited May 23 '17 at 12:14

If you're always going to be working in the current month and all the data is formatted with the full month name and the two digit year you could replace this line:

Sheets("APRIL 15").Select

with this one:

Sheets(ucase(format(now(), "mmmm YY"))).Select

If you need to have more flexibility in the timing you can replace now() with a date variable from somewhere else in your code. If the tab name formatting uses some abbreviations for the longer month names you may also have issues with my suggestion.

Something to add to improve the code overall would be to read this:How to avoid using Select in Excel VBA macros

Edit

Yes you can use that range. There are a lot of ways to get that information into your procedure. It can get confusing since you're still using activate and select and it's harder to keep track of each sheet. I might change it to something like:

Sub test()

    Dim codeWorkbook As Workbook
    Dim dataWorkbook As Workbook
    Dim dateRange As Range
    Dim sheetName As String

    Set codeWorkbook = ThisWorkbook
    Set dataWorkbook = Workbooks.Open(Filename:="X:\GLOBLOPS\TRADE_PROCESSING\Procedures\Corporate Action\Full Call\Full Calls Tracker.xls")
    Set dateRange = codeWorkbook.sheets(1).Range("WHERE_EVER_DATE_IS")
    sheetName = UCase(Format(dateRange.Value, "mmmm YY"))

    dataWorkbook.Sheets(sheetName).Columns("A:I").Copy Destination:=codeWorkbook.Sheets(1).Range("A1")
    'change the sheet and/or the range to what you need it to be
    dataWorkbook.Close False

End Sub

Thanks this has been helpful. My only other question would be I do have a cell in the workbook that has =Today() to pull current date for an if funtion i am using. Could I use that cell instead of the =now() and would that help with possible formatting issues? — Kyle, Apr 24 '15 at 17:52
Great this is very helpful and answers my questions. Thank you. — Kyle, Apr 24 '15 at 18:35

Pulling current months sheet to a new workbook based on the current month

1 Answers1

Edit