You don't say in what way your program is not working for you. On my system (Windows running perl 5.15) there were a few problems.

I added use strict;, which I do habitually. This revealed one fault and I made two other changes to avoid warnings. Then the program ran but produced an empty file, so I added an explicit close() and it created worksheets as expected. I ended up with the following:

use strict;
use warnings;

use Spreadsheet::WriteExcel;
#use Spreadsheet::ParseExcel;
#use Spreadsheet::ParseExcel::SaveParser;
my $workbook;
my @s;
my $Month="Sept";


sub create_excel {

    $workbook = Spreadsheet::WriteExcel->new('FAISTATS_'."$Month".'.xl
+s');

    for ( my $i=1;$i<=8;$i++) {
        $s[$i] = $workbook->add_worksheet( "cpzea0".$i."a0001" ) ;
    }
    $s[9] = $workbook->add_worksheet( "WEEKLY" ) ;
    $s[10] = $workbook->add_worksheet( "MONTHLY" ) ;
}


create_excel();
$workbook->close();
[download]

It may help you to review the caveats about close() in Spreadsheet::WriteExcel.

See "User-defined subroutines" section in Learn Perl in about 2 hours 30 minutes

See "The Default Array Variables" and Chapter 5 "Functions" in the free book Modern Perl a loose description of how experienced and effective Perl 5 programmers work....You can learn this too.

#!/usr/bin/perl --
use strict;
use warnings;
...
Main( @ARGV );
exit( 0 );
sub Main {
   my $file = shift || 'thefyle.xxx' ;
   my $workbook = create_workbook( $file );
   ...
}
sub create_workbook {
    my( $filename ) = @_;
    my $workbook = ...;
    ...
    return $workbook;
}
[download]

Tutorials: Variable Scoping in Perl: the basics,
Coping with Scoping , Mini-Tutorial: Perl's Memory Management,
Lexical scoping like a fox,
Read this if you want to cut your development time in half!,
Closure on Closures , perlref#Circular References,
Memory leaks and circular references,
Circular references and Garbage collection.,
make perl release memory,
about memory management , Re^3: Scope of lexical variables in the main script , Re: What's the weaken here for?,
Devel::Cycle,
Devel::NYTProf,
Devel::Leak Devel::LeakTrace,
WeakRef

see Re: No such file or directory error/No such file or directory error, see template at (tye)Re: Stupid question (and see one discussion of that template at Re^2: RFC: Creating unicursal stars

Note: this appears to be a near duplicate (other than formatting and a couple of minor revisions of the code) of Not able to create worksheets inside a subroutine.