Hi all,
I am facing a problem regarding the range selection.
I have a worksheet used for recording daily stuff. SOmething like
this:
ColA ColB ColC
January 1.1.2011 9:00 - 10:00
January 1.1.2011 10:00 - 11:00
January 1.1.2011 etc.
January 2.1.2011
January 2.1.2011
January 2.1.2011
January 3.1.2011
January 3.1.2011
January 3.1.2011
etc.
Other columns contain daily specific data.
At the end of each month I have to create reports on what was done in
that month. Hence I need to copy the range for all stuff in that month
(for example January) and paste it to another worksheet in the same
workbook.
My problem is that I can't use fixed ranges, because of the February
29th.
I tried several things before posting here:
1) I wrote macro to copy and paste the entire range (all months) to
the new sheet and have it delete all entries which didn't contain the
month in question. This turned to be a pain due to 3500 and some rows.
2) I wanted to search for the required month and loop over all rows,
but this would also be time consuming.
3) Then I tried filtering the range to display only entries with
respective month, however I encountered date format issues:
Macro for April recordes like this:
ActiveSheet.Range("$A$1:$AS$3653").AutoFilter Field:=3, Operator:=
_
xlFilterValues, Criteria2:=Array(1, "4/1/2011")
I modified this to:
ActiveSheet.Range("$A$3:$IN$3653").AutoFilter Field:=3,
Operator:= _
xlFilterValues, Criteria2:=Array(1, MyMonth)
Whereas MyMonth is the variable defined earlier so I can use one macro
for all 12 months.
Here the format is the problem. My locale entry convention is:
dd.mm.yyyy
But the macro syntax only works if I put in: mm/dd/yyyy, which is not
the format used in our country.
I also tried to modify the format with: MyMonth= Format(MyMonth, "mm-
dd-yyyy")
but I always get the date displayed with full stops and not with
slashes.
How can I fix this formatting issue or maybe in a more efficient way
select the required range for desired month?
Thank you for your help.
seba
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
