Hi Monks,
I checked the tutorial on perlmonks regarding data copying in excel using perl.
However, i did not get the answer i was looking for
Here's what i'm trying to do:
I've an excel workbook with a lot of sheets, i'm trying to get the data from a few of the sheets
to a new sheet within the workgroup. I'm unable to do it..
This is hte code i've written, plz let me know why it's failing

my $Book = $Excel->Workbooks->Open("$input_file") || die "could not op
+en excel file";
my $sheetcnt = $Book->Worksheets->Count();
$sheetcnt++;
my $New_Sheet = $Book->Worksheets->Add({After=>$Book->Worksheets($Book
+->Worksheets->{Count})});
print "Sheetcount = $sheetcnt\n";

####30################################################################
+##############

my $count = 0; ########Counting the sheet number, if first sheet, then
+ take the whole data, else exclude first row

foreach my $Sheet(in $Book->{Worksheets})
{

    my $select_sheet = $Sheet;    
    
       
    if($select_sheet->Range("A1")->{Value} =~ /Step/i)
    {
                        my ($last_row,$last_column) = sub_find_last($S
+heet);
            print "$select_sheet->{Name} $last_row,$last_column\n";
        last if ($count == $Book->Worksheets->{Count});
        if($count==0)
        {            my $copy_range = $select_sheet->Range("A1:$last_c
+ol");
            $select_sheet->copy($copy_range);
            my $paste_range = $New_Sheet->Range('A1');
            $paste_range->paste();
            $count++;
        }
        else
        {
            my ($last2_row,$last2_col) = sub_find_last($New_Sheet);
            print "$last2_row and $last2_col are here\n";
            my $range_needed = $last2_row + 1;
            my $copy_range = $select_sheet->Range("B1:$last_col");
            $select_sheet->copy($copy_range);
            my $paste_range = $New_Sheet->Range("$range_needed");
            $pasted = $paste_range->paste();
#            $Book2->Save();
            $count++;
        }
        
    }
    

}



##########Subroutines###########

####sub find_last########
####Used to find the last row/column in the sheet

sub sub_find_last 
{
    my $sheet_sel = shift;
#    print "$sheet_sel is the sheet selected\n";
    my ($last_rows,$last_col);
    $last_rows = $sheet_sel->UsedRange->Find({What=>"*",
                SearchDirection=>xlPrevious,
                SearchOrder=>xlByRows})->{Row};
    $last_col = $sheet_sel->UsedRange->Find({What=>"*",
                SearchDirection=>xlPrevious,
                SearchOrder=>xlByColumns})->{Column};
    return ($last_rows,$last_col);
}
[download]

UPDATE:::
The script works fine if i only try to find the sheets which have "Step" in A1 cell... However, when I try
to process the sheets for copying/pasting the cells to the new sheet (last sheet), it fails...
I get the output (command-line) which is similar to:

Sheetcount = 49
Sheet18 3,6
Sheet20 2,6
Can't use an undefined value as a HASH reference at script.pl
[download]

Update2 :::
Solved the problem... A bit of using warnings/strict gave the clue and an old solution on
perlmonks (ironically, provided by me id:781893:(), helped to clear the air...
I've updated the code below:

foreach my $Sheet (in $Book->{Worksheets})
{

    my $select_sheet = $Sheet;
    if($select_sheet->Range("A1")->{Value} =~ /Step/i)
    {
        print "CounT IS $count \n";
        my ($last_row,$last_column) = sub_find_last($select_sheet);
        print "$select_sheet->{Name} $last_row,$last_column\n";
        if ($cnt == $num)
        {
            last;
        }
        elsif($count == 0)
        {
            print "I am here";
            my $rango = "C"."$last_row";
            print "$rango is the rango";
            $select_sheet->range("A1:$rango")->copy();
            $New_Sheet = $Book->Worksheets($sheetcnt);
            $New_Sheet->range("A1")->Select;
            $New_Sheet->paste();
            $Book->Save();
            $count++;
        }
        else
        {
            my ($last2_row,$last2_col) = sub_find_last($New_Sheet);
            my $range_needed = $last2_row + 1;
            my $rango2 = "C"."$last_row";
            my $rango3 = "A"."$range_needed";
            $select_sheet->range("A2:$rango2")->copy();
            $New_Sheet->range("$rango3")->Select;
            $New_Sheet->paste();
            $Book->Save();
            $count++;
        }
        $num++;
    }
    else
    {
        $num++;
    }

}
[download]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.