Loop for copying each row in one column into multiple worksheets

**TagaExcel** · 03-30-2013, 04:34 PM

Hi everyone, I'm new to the forum and I have limited experience with excel macros. Working on trying to automate our accounts statement process where we take the output of our acounting system (where the data for all accounts are lumped together), and extracting the information for each account so that we have one statement per account.

I've been able to delete blank rows and put all account numbers into another spreadsheet so that I know how many statements are needed for processing. I now have to be able to put the account numbers into each spreadsheet so that we can do a vlookup in the customer database to pick up the addresses as well as look at the original output file to also pick up any transaction lines associated with the account.

I have all account numbers in one column on one worksheet and am able to automatically create a new worksheet (which is in the statement format that we want to create) based on the number of accounts. What I want to do now, is enter each account number in the first worksheet and populate the multiple worksheets with each account number. I need to be able to loop down the column on the first worksheet until I come across a blank row and for each row, populate one worksheet (in the same workbook) and then the next and the next until we get to the last worksheet. See below:

In one column I have multiple account numbers:

123456
245678
341875
567890
567894
123457

In the same workbook, I've already created 6 worksheets (based on the above number of accounts) named Statement (1), Statement (2) to Statement (6). I need to be able to create a macro to take each of the account numbers from the first worksheet and add them to the same cell in each of the 6 worksheets that are in the same workbook.

Does anyone have some "simple" code that I can use that is very easy to understand? Hope to hear from someone soon.

**AB33** · 03-30-2013, 04:54 PM

Very confusing! Please attach a sample book with a desired result.

**Richard Buttrey** · 03-30-2013, 05:09 PM

Hi,

What's the ultimate aim here? And are you sure you're going about this in the most effective way.
On the face of it it seems that you have a database of customer records and you want to create a statement for each customer and presumably Print it, (or perhaps eMail it - or both)

In which case I personally wouldn't create a sheet for each customer.
The way I always approach these things is to create a template statement on a second sheet and a list of customer codes/names on another. I then use an Advanced Filter macro to loop down the list of customers, populate the statement template with the details, taken from the accounting database sheet, Print it, (or Save as a file or eMail it) and then move on to the next customer.

This is much simpler and tidier than replicating records across many sheets and keeps the file size to a minimum.

**TagaExcel** · 03-30-2013, 06:41 PM

Hi guys, and thanks Richard - you're probably right. The thought had crossed my mind that by creating so many sheets, it could create an issue with file size as there could be up to a hundred accounts. Yes, the idea is to create a separate statement for each account that we can print or email out to customers. It's a huge project that I've taken on, but am hoping I can get some help from users of this forum and will be great experience for me.

So, just to clarify, I have an output fie (from the accounting system) which I've attached. I need to take data relating to each account and populate a general statement template with the account number and the relating transactions. I then need to create a lookup which will read each account number from a customer database - where there is a match, it returns the address to each statement.

I hope that all makes sense. What I've been able to do so far is remove all the empty lines from the attached output file and copy over account numbers into another workbook. This new workbook has the account numbers on one worksheet and a statement template on another worksheet within the same workbook. I now need to be able to do the following:
1 Take each account number and update the statement template
2. Using the account number, read off an address database (which looks like the following)

Col A B C D E
123455 15 New Line Dublin County Dublin IRELAND
123456 O'Connell St Wexford County Wexford IRELAND
123457 Sandycove Co. Cork IRELAND
etc

3. Using the account number, read off the attached output file and copy over all transactions relating to that account.

Can anyone give me a simple solution to this that's easy to understand? Thank you!

**Richard Buttrey** · 03-30-2013, 07:56 PM

Hi,

Another question first.

The attachment you uploaded, whilst it has been downloaded from your back office system, is a typical .prn file with the Print layout that's in place when reports are printed from the accounts system. These sorts of reports are not very useful as a database for use in Excel. At best they need to have all the page break formatting, banner headings, column headings, blank rows, sub totals etc. stripped out first. Whilst this can be done by writing an Excel macro to do it, the first thing to check is whether the accounts system can output a user friendly Excel record report which just contains the line item (Document No) details.

Any self respecting accounting system should be able to do this, and even if there's not one on the menu, often these proprietary packages offer an ad hoc reporting tool for users to define their own reports. Usually end users can do this fairly easily but you might find you need a friendly IT support teccy type to define it.

Either way, check whether this is available first. You're looking for a report that contains everything relative to a record on a single line, i.e. Customer Code, and then all the stuff you see on rows like 10, 25, 40-44 etc. If it is then upload that instead.

**TagaExcel** · 03-30-2013, 08:22 PM

Hi Richard, this is the only output file that the system produces and every month, the staff normally just copy and paste each of the transaction details into the statement template manually, which as you can imagine is a very time consuming task.

What I was going to do, was re-format the output file to a more usable report e.g. after removing all blank lines (already done), remove all rows where there is a non-blank cell in column A or B. This would be done after moving all the account numbers over to the workbook that has the statement template in a separate worksheet. Then it would only have header and transaction row (where there is a Document No.) Then all I'd have to do was:
1. Paste over the first account number to statement template (from the list of account numbers copied into one column)
2. Find the first set of transactions on the attached
3. Vlookup address details
4. Save to new file and print
5. Go to next account number and re-do above process

What do you think?

**Richard Buttrey** · 03-31-2013, 07:30 AM

Hi,

Here's what I'd do as starters. Would you do this manually with a larger set of your data so that we can prove the concept. If this results in a simple table of document records then we can write a macro to do it automatically, and then if you can provide the statement template that you want to fill for each customer, and the customer name/address record database, we can put the two together.

Starting with your Statement data report add a single new blank row 1.
Now add two new blank columns A & B
Now in A2 enter the formula

Formula:

Please Login or Register  to view this content.

and in B2 enter

Formula:

Please Login or Register  to view this content.

Copy A2:B2 down your whole set of data.

Now filter the whole sheet for the word '"Keep" in column B. This should result in a simple list of all transaction records and in column A the Customer number.

Presumably the report has a grand total in the 'Amount in DC.' column. Make a note of this and with the data still filtered put an

Formula:

Please Login or Register  to view this content.

where nn is the last numbered row. Check that this subtotal of the filtered rows agrees with the report total.

Assuming that's the case then the next step in the automated process will be to filter column B for all the 'Delete' rows, select them and delete them

But one step at a time.

**TagaExcel** · 03-31-2013, 05:12 PM

This is great Richard. All of the above worked where those rows showing "Keep" in column B are the rows with the transactions that are needed to put into each statement. The grand total in 'Amount in DC' column matches the amount calculated by the subtotal formula too

I've uploaded the statement template and a snapshot of the customer address database. I really appreciate your help on this.

**Richard Buttrey** · 04-01-2013, 06:31 AM

Hi,

Try the attached.

The Data sheet is where you will bring in a new set of data. Click the button and then select your Statement Data file.

The Cust Database is your list of customers with an additional column A where you can pick which statements you want to print. Clicking a cell in column A will select that row and you'll see a tick. Clicking again it will show a cross. Mark which ones you want to print. You can also select All statements by clicking the 'Select All statements' or deselect all by clicking the same button (it will read 'Deselect All statements).

To print a limited selection, first Select All and then untick those you don't want.

On the Statement sheet there is a drop down box where you can select a particular customer's statement. Click the 'Print Statement' button to print it.

**TagaExcel** · 04-01-2013, 02:11 PM

Wow Richard! I'm so amazed - I've just done the process once and am so impressed with how simple you've made it all look and your thought process behind it! I really appreciate your help with this.

I understand how you load the data - you've removed all the unnecessary lines and added the customer number to the first column so that we know which customer the transaction relates to.

The address on the Statement tab reads off the customer database. I'll have to go through all of your macros in depth and make sure I understand the code (and I doubt I'll understand all of it). One thing I don't see straight off is how the statement tab updates with the transacction information from the data tab?? And am I correct in saying that you can either print via the Cust Database page (if multiple statements) or from the statement tab (if only printing one or two).

**Richard Buttrey** · 04-01-2013, 02:35 PM

Hi,

Glad you found it useful and informative. Yes you can print a statement from either of the two tabs. As you noted the Cust Database tab allows you to select one or many.

The statement tab updates automatically every time you make a selection in the drop down cell. There's an event driven macro attached to that cell which performs the filtering of data from the database.

**TagaExcel** · 04-01-2013, 03:22 PM

Hi again, I'm doing some testing with real customer address and account transactions and I'm getting a run-time error when I select a customer from the drop down menu on the statement tab. It seems to be relating back to the FilterData code. Any ideas why?

**TagaExcel** · 04-01-2013, 03:25 PM

I see you have some named ranges which is likely the issue...

**Richard Buttrey** · 04-01-2013, 03:31 PM

Hi,

Have you changed any of the column labels on the "DataOut" range, i.e. A33:G33 on the Statement sheet, or any of the column labels in B5:I5 on the Data sheet.

Upload the workbook if you prefer.

**TagaExcel** · 04-01-2013, 04:28 PM

Hi, that's it - I had changed the name of one of the labels!

**Richard Buttrey** · 04-01-2013, 04:32 PM

That'll teach you.

If there's one thing that macros aren't, it's forgiving.

Loop for copying each row in one column into multiple worksheets

LinkBack

Thread Tools

Rate This Thread

Display

Loop for copying each row in one column into multiple worksheets

Re: Loop for copying each row in one column into multiple worksheets

Re: Loop for copying each row in one column into multiple worksheets

Re: Loop for copying each row in one column into multiple worksheets

Re: Loop for copying each row in one column into multiple worksheets

Re: Loop for copying each row in one column into multiple worksheets

Re: Loop for copying each row in one column into multiple worksheets

Re: Loop for copying each row in one column into multiple worksheets

Re: Loop for copying each row in one column into multiple worksheets

Re: Loop for copying each row in one column into multiple worksheets

Re: Loop for copying each row in one column into multiple worksheets

Re: Loop for copying each row in one column into multiple worksheets

Re: Loop for copying each row in one column into multiple worksheets

Re: Loop for copying each row in one column into multiple worksheets

Re: Loop for copying each row in one column into multiple worksheets

Re: Loop for copying each row in one column into multiple worksheets

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions