To optimize the SELECT, you need an index on MONTH(Date1) and YEAR(Date1). Then the following should work for you:

lcFolder = [d:\data]
if len(lcFolder) > 3 and not directory(lcFolder)
   md (lcFolder)
endif
select distinct year(date1) as nYear from Weights into cursor crsYears
select crsYears
scan
   lnYear = crsYears.nYear
   for lnI = 1 to 12
       lcFile = transform(lnYear) + padl(lnI, 2, [0])
       SELECT * FROM weights ;
          WHERE MONTH(date1) = lnI and YEAR(Date1) = lnYear ;
          INTO TABLE ADDBS(lcFolder) + lcFile
   endfor
endscan

>Dear Experts
>
>I have a table entitled "WEIGHTS", index on field "DATE"
>
>The date field has data as
>
>24/07/2004
>02/08/2004
>11/09/2004
>01/01/2005
>15/02/2005
>
>I want to divide this table into 12 tables, every table will hold data of a different month. For example the first table will be 072004.
>
>For this I wrote following codes
>

>FOR p_count = 1 TO 12
>    file_name = ALLTRIM(STR(p_count))
>       SELECT * FROM weights WHERE MONTH(date1) = p_count INTO TABLE d:\ahsan+(file_Name)
>NEXT
>CLOSE ALL
>

>But above codes retreive data of only last month as well as create one dbf instead of 12.
>
>Note: Index is on DATE field.
>
>Please help