Variable Dates in VBA

[mjmcder]

Hi all,
I am a simpleton with VBA and have a question regarding dates in VBA. I have a large financial workbook with many tabs of data, and am trying to compile a management summary report. I have a "processor" tab where I run all of my macro's from. Here on the processor tab, I have the formula =MMM "&FYNum where FYNum is a named cell that contains the Fiscal Year (we don't operate on a calendar basis so this is needed). so the date appears as "Sep 2017", "Oct 2017", etc.

I then have numerous tabs with a bunch of data, and this data is broken out by month end date (utilizing the same formula as above). so columns B, C, D are cost, market value, and gains/losses for a particular product or investment, and above those three columns is the date formula.

When I store the original date value as a variable from the processor tab, I am unable to do a "find" on that value on the data tabs. Does anyone know how to help here...and I really hope this makes sense. Apologies for the confusing description.

Thanks in advance!

 

[healey21]

Welcome to the forum,

Please don't look to double post your question. https://www.mrexcel.com/forum/excel-questions/1024170-find-date-formula-vba.html you can use a bump instead to help find a solution.

Does your date actual appear as text entries or true number entries? What VBA code have you tried so far?

 

[mjmcder]

My apologies for the double post. Didn't think the first one went through, but I guess it did.

I have formatted all date fields as "General".

I have only begun attempting to verify the "find" function would work, so the code I tried was this..

Sub practicemonthenddate()
Sheets("OPEB Monthly Recon").Select
Range("MonthEndName").Select

MEDATE = ActiveCell.Value

Sheets("HI BNY Asset Detail").Visible = True
Sheets("HI BNY Asset Detail").Select

Cells.Find(What:=MEDATE).Activate
ActiveCell.Offset(1, 0).Select


End Sub

Thanks for your help!

 

[healey21]

If your date format is General then it should show all dates as serail numbers. i.e. example todays date is 25/09/2017 when formatted as General it shows it as 43003, is this what you see?

 

[mjmcder]

on the main "processor" tab where I manually enter the date, the exact key strokes are 'MMM YY. So I am adding that apostrophe beforehand to keep it from converting (I guess this is changing the value?).

For all the subsequent data and reconciliation tabs, the dates are formula driven (so as I move through fiscal years, I don't have to change any of my date headings). The exact formula (where MMM is the first 3 letters of each month) is ="MMM " & FYNum. So all the dates on all the data/recon tabs are actually formulas.

 

[mjmcder]

At the basis of what I need (because I know I am not describing it very well), I need to create a monthly report on a separate tab within a workbook, where the data being grabbed for that report depends on what month it is.

So I would like to grab all the data for August (from multiple worksheets) and have it placed on a standard reporting template on another tab for August data, and then use that same template the next month for September data. etc. etc.

 

[healey21]

An example of the code is shown here it is using a filter for this month and adding to a new sheet. Adjust the sheet names. It will only do it on the one sheet for now, test it and then let us know for the final stage. I have commented as much as I can to help you.

Sub MonthDateFilter()
'Switch screen updating off
Application.ScreenUpdating = False
'Filter the data based on ThisMonth
Sheets("RawData2").Activate
Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:= _
xlFilterThisMonth, Operator:=xlFilterDynamic
'Copy the filter data
ActiveSheet.AutoFilter.Range.Copy
'Inserting new worksheet to the end of the workbook
Worksheets.Add after:=Worksheets(Worksheets.Count)
'Pasting the Data
ActiveSheet.Paste
'Autofit columns
Selection.Columns.AutoFit
Range("A1").Select
'Activating the "RawData2" sheet
Sheets("RawData2").Activate
'Removing filter from the worksheet which we applied earlier
Selection.AutoFilter
Sheets("CheckDates").Activate
MsgBox "This months data has been placed onto a new sheet", vbInformation, "Test"
End Sub