Summarizing to New worksheet

[max57]

Hello All,

I have a Petty Cash Workbook that consists of a Cash Receipts worksheet, and two separate credit card worksheets. I would like to be able to summarize the data from these three worksheets to a fourth worksheet called “Yearly Totals”.

I’ve copied and modified code from Ron deBruin’s website but I’m not getting anywhere with it.
I’m constantly running a Runtime ERROR 9 with this line:

        
  Set DestSh = Worksheets(“Yearly_Totals”)

I also wanted to modify the code to use the current region property in order to pick up a bordered range, which you’ll see when you open this booklet. This would work better than the standard last row of data formula in this instance.

        
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

I’m pretty much an intuitive cut/paste kind of person so this is throwing me for a loop. (Literally and figuratively )

I’ve attached a formatted workbook for your perusal.

I would appreciate any help or explanation regarding this.

I have one other question regarding this workbook, but will post it to another thread if I can get past this design problem.


Thanks!

Mark

[Huron]

Hi Mark

The runtime error is because your worksheet tab doesn't have an underscore between Yearly and Totals.
The Cash Receipts sheet is set up completely differently from the other sheets so it won't make any sense if you paste this data into the Totals sheet. To omit this sheet, you can add it to the array in the IsError line.

If IsError(Application.Match(sh.Name, Array(DestSh.Name, "CASH RECEIPTS"), 0)) Then

To use Range.CurrentRegion you will need to put a blank row above the data in sheets to be copied, as well as the blank column B that you've already added.

[max57]

Thanks Huron!
That did the trick.