Copy Worksheets and Formulae

[rcbricker]

There is an example of the spreadsheet attached.

Basically the sheet works fine with the formulas, however, there are a few things that will become cumbersome over time. The sheet has to be continued for each new month.

Column A (first sheet) has the initial values that are either credits or debits that exist by the company. Each row is a different company that our company owes or is owed. (names removed for confidentiality)

Column C is the concienment value that remains constant for each month.

Column D is the percentage owed by our company

Column E is the actual value owed

Column F - I are for recording weekly payments

Column J is the new credit/debit balance

Column A for all sheets but the first is a copy of the previous sheets column J.

This sheet feels clumsy and I think that instead of copying the worksheet each month that a VBA can be created that will create a new worksheet (promt the user to name the sheet) and have each of the columns copied over with the same working formulas. (unless someone can come up with a better format for this sheet. I think it looks badly bu cannot for the life of me figure out the formulas needed to clean it up) thanks for the help.

[Zack Barresse]

Hi,

I would suggest a total restructuring of the workbook. I would suggest having one sheet with an entry location of all raw data. Then use other sheets (and macros) to run a formatted report or data query (however you want to see the data) from that information.

I'd probably base it off of how often the information is entered and how much information (different types) is entered each week (or other time frame). If weekly, maybe have weeks going across a top row and location (allocated to/from) down the column, then have your matrix of infomration in between.

Depending on the size, you may want to think about where you want to seperate the workbooks also. If not too large, shall you have one year per sheet? Should you have one month per sheet? Would you want every year as a seperate file? Remember, the number of sheets is not limited (only by your pc's memory) but should be kept within reason. 101 sheets gets hard to navigate and/or troubleshoot.

If you got all of the information about what needs to be entered, I'm sure a good example or template would emerge.

[Anne Troy]

From what I can see, you have no way to track the payments by date. You also seem to have no way to track if someone makes more than 4 payments.

I would put payments on one worksheet.
I would put summary/balance on another.

You'll use SUMIF to sum the payments between date and date.

That should get you started, no?

[Anne Troy]

Truth is, you have a database application, and you're using Excel for it. But that's cool if that's what must be.

[mdmackillop]

Try the attached. I've taken some liberties with the names etc, and if you require more entries, a slight adjustment is required, but the code is straightforward.
BTW, I agree with the foregoing comments, but this may keep you going until you have things "sorted".
MD

[Anne Troy]

Nice, MD. As usual.

[mdmackillop]

Having read Firefytr's comments re the number of sheets, I've revised my code to PasteSpecial/Values the previous sheet. This freezes the previous values so that the latest sheet is not affected by changes in the older sheets (or accidental deletions), and prevents a daisy chain of links from sheets 1 to 101 or whatever.

Praise is always welcome DB. Hope that you're keeping well.

[rcbricker]

I appreciate everyones help AGAIN. you guys always reply so quickly.

mdmackillop thanks for the work it is great. I will let the guy who is in charge decide if he is going to use it or not. I appreciate the help.

I agree with you all which is probably why I couldn't come up with an easy fix that made it all look and run better.

Thanks