The error is now gone!

Could you point me to the right direction?

Thank you for your help!

my $source = $book->add_worksheet($source);

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]

poj

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.