This line suggests you are getting confused between the source and the target. Try using more descriptive variable names for each workbook/worksheet. Try

#!/usr/bin/perl
use strict;
use warnings;
use Spreadsheet::WriteExcel;
use Spreadsheet::ParseExcel;

my $path   = '/abc';
my $output = $path.'/one.xls';
my $wb_target  = Spreadsheet::WriteExcel->new($output) 
  or die "Cannot create new Excel file: $!";;
  
my $parser = new Spreadsheet::ParseExcel;

my @states = qw(texas michigan hawaii florida kentucky);
for my $state ( @states ){
  my $abbr = substr($state,0,2);
  print "Creating sheet $abbr in $output\n";
  my $ws_target = $wb_target->add_worksheet(uc $abbr);
 
  my $source = "$path/$state/$abbr.xls";
  print "Copying $source\n";
  my $wb_source = $parser->parse($source) 
     or die $parser->error();
     
  ws_copy($wb_source->worksheet(0),$ws_target);
}
$wb_target->close();

sub ws_copy {
  my ($source,$target) = @_;
  my ( $row_min, $row_max ) = $source->row_range();
  my ( $col_min, $col_max ) = $source->col_range();

  for my $row ( $row_min .. $row_max ) {
    for my $col ( $col_min .. $col_max ) {
       my $cell = $source->get_cell( $row, $col );
       next unless $cell; 
       $target->write( $row , $col, $cell->unformatted );
    }
  }
}
[download]

Hi poj ! Thank you for the code. It was short and sweet, eliminate all the long-winded code that I had.

I will remember to give more descriptive name for variables, but sometimes Idk how to name them, im bad at it

I got the new data, now the different xls files are at "$path/$modes/result.xls"

They are all sharing same name but in different directory.

Also I am concern on how to change the sheet name? Instead of taking the substring from the states' name? Coz for states like South Carolina, South Dakota and etc, I wasn't sure how to name it accordingly, I'm planning to name it as the same in postal code.

Apologize for the changes, I should've know the file/directory/name would have different pattern from time to time.

Take a look at File::Find::Rule. For example

#!/usr/bin/perl
use strict;
use File::Find::Rule;
use Data::Dump 'pp';

my $path = '/abc';

my $rule =  File::Find::Rule->new;
$rule->file->name( 'result.xls' );

my %source=();
for my $filename ( $rule->in($path) ){
  my @f = split '/',$filename;
  my $mode = $f[-2];
  $source{$mode} = $filename;
};

pp \%source;
[download]

poj