Repeat form X times, where X is the number of rows containing data in a separate sheet.

[BB1972]

Hey all,

Hoping someone can help me out here with a small project I've been asked by a colleague to assist with.

In the attached workbook, there is a tab called FORM, which contains a form. The form gets its data from the DATA tab - an ID # can be input in Cell A1 (TABS), which corresponds to ID#s in Column A (DATA), then INDEX/MATCH does the work.

What I would like to achieve is for the form in the FORM tab to be repeated only by the number of rows of data on the DATA tab. The number of rows on the DATA tab changes throughout the year, from a handful (4/5) to thousands, and as 1 form per row of data will be printed, it would be great to have the corresponding number of forms for rows of data.

I'm fairly certain I could achieve this with some conditional formatting, but think this would be a very inelegant (and a technically horrendous!) way to go. My VBA skills are next to nothing, but I get the sense that a VBA solution is probably the best way to go, and I've no doubt this will be child's play to some of you reading this.

Could somebody help me with this, please?

[arlu1201]

This is what i understood -

Either you are looking at - creating individual sheets for each row of data in the file
or
have the macro put in the ID# in A1 of the Form tab print the page, then put in the ID# of the next row and print, etc.

Which option do you need?

[BB1972]

Hi Arlu,

Thanks for the response.

Sorry - I've just realised that I wasn't explicit about the result, so I've updated the workbook to show desired results. Ideally, the form would repeat below the first one (until there is no more data, as in the attached). If that isn't possible, then I think the latter of your options would be preferable - I don't think there's a real need to make (potentially) thousands of sheets within one workbook.

[MarvinP]

Hi Brendan,

Find the attached where you only need a single form, because you've done most of the work with your formulas. If you simply change the number (in yellow) it will show the data correctly. Then print out the sheet. Click on the button I've created and watch it go through your forms for a second each. If you comment out my Wait statement and put in a Print code it will run through all our data printing out the form for each.

I think this is what you really want.

[BB1972]

Hey Marvin,

That is great, thank you.

I don't wish to seem ungrateful (and this is now natural curiosity as much as anything else!) but - would it be possible to repeat the form underneath the first form, as outlined in post # 3? I presume it is, and I've had a look at your code where you declare(?) the LastID and IDCtr as doubles - I imagine that the LastID would also be the number of "forms" needed - I just have no idea how to tell Excel (using VBA) to copy the form, then repeat it that "LastID" number of times.

I think that is what I really want but thank you for your effort so far, I do appreciate it.

[MarvinP]

Hey Brendan,

If you copy the form down then worksheet, it just makes the problem harder. In VBA you would need to copy and Paste lots of times. It is so much easier to do the form only once and base it on the number in A1. I really don't want to make your problem harder. I'm into EASY....

You add-on question is like saying "Yes your answer works, but can you do it in a harder way?". I most likely could do this problem in lots of ways using lots of different VBA code or Excel functions/formulas. But why? Give my answer a try and see where it takes you. Learn a little VBA. Record a macro and see what it says in VBA.

Start here http://www.cpearson.com/excel/Debug.htm

[BB1972]

Hi Marvin,

You add-on question is like saying "Yes your answer works, but can you do it in a harder way?".

Not at all - my add-on question is saying "Your answer works, but can this be done the way I have asked to do it?"

I think this is what you really want.

I wanted to avoid being discourteous when replying to that; however, your assertion regarding what I really want is incorrect - what I really want is outlined in post # 3.

Purely for the sake of debate/discussion: If I had phrased my question differently, without mentioning printing, and asked for the data to populate as many forms as required, I'm certain you wouldn't say that that can't be done, or it's too hard, so here's a solution where each the data pops up on one form, for a second at a time - I'm confident that you, or someone else on the forum would say "Sure - here's how we can do this." That, however, may say as much about my lack of knowledge of VBA as it does about my blind optimism

Learn a little VBA. Record a macro and see what it says in VBA. Start here http://www.cpearson.com/excel/Debug.htm

I will! I think it's time to have a look into it, and see where it takes me. Thank you for the link; and despite the fact that your solution is not quite what I'd like it to be, thank you (that isn't always said enough around here) for the time and effort that you put into replying.

[MarvinP]

Hi Brandan,

Excel works best when it's data is kept in tables. That is there are no blank rows or columns in a range of cells. We get lots of questions that want the "good" table design to be displayed or extracted to different worksheets or workbooks. This is like taking a step backwards from how Excel works. I saw you problem as starting with a "good" table and wanting it to be downgraded by making multiple forms on a single sheet or many sheets. As soon as this is done then Filtering the data or AutoFilter or Pivot Table no longer work. It is just the wrong direction to go.

I believe if you learn more about Tables, Filters and Pivot Tables you will realize some of the power Excel has to offer. You showed you aren't a beginner because of the formulas you had on your form sheet. I just simply thought this was the wrong direction to go. I hope the short VBA code that increments the value in A1 would allow you to get your "good" table data to a form design that you wanted. To extend it down the page with multiple forms, in my opinion, was simply a step backwards.

Maybe one of the smart guru's can get exactly what you are looking for. In my opinion a single form using the formulas you already have is a better solution. Sorry if my answer isn't exactly what you were after. I hope it gives you some food for thought on merging your needs to how Excel works best.

[BB1972]

Hi Marvin,

Excel works best when it's data is kept in tables. That is there are no blank rows or columns in a range of cells.

As a general rule, I agree entirely with that sentiment. However:

I saw you problem as starting with a "good" table and wanting it to be downgraded by making multiple forms on a single sheet or many sheets. As soon as this is done then Filtering the data or AutoFilter or Pivot Table no longer work. It is just the wrong direction to go.

There won't be any filtering etc of the data on this sheet - it's just to view, and then print the data; and, respectfully, if an OP is aware of any shortcomings or pitfalls of a method they want to use, surely it's up to that OP to decide whether or not to proceed in that direction if it suits their needs, no?



BUMP

I'm going to rephrase my query for anyone who may be able and willing to help me: I would like to copy rows 1:59 of the FORM tab, and paste from row 60 n times - n being the number of rows in Column A of the DATA tab that contain data. Could somebody help me achieve this, please?

[MarvinP]

OK Brendan,

Here is what you asked for. Open the attached and find a second button on the Form sheet. Press it and it will do what I think you want.

Even though I think this is a step backwards, you might read the code and learn a little bit from it. If you set a breakpoint in the code and step through it, it might make more sense.

[BB1972]

Marvin,

"Sub DoAsYouAreTold()" - nice

Thank you very much - that is just what I wanted. I'm very appreciative of your assistance.

[BB1972]

Hi again, Marvin - I just wanted to let you know that I passed that spreadsheet on to my colleague today, and he was delighted with it. Thanks again for your help.