Macro to insert extra rows in a table

I am a neophyte to writing macros, but I think this may be a good opportunity
to learn. I have a template that I am creating for creating a simple report.
It consists of multiple worksheets with three or four tables per worksheet.
For a given project, I may need these tables to have 5 rows. For another, I
may need it to have 12 rows, and so on. I have created a worksheet where I
enter the number of rows needed for the tables in a cell (say, "C5"). I would
like to create a macro that automatically goes through the worksheets and
adds enough rows to each of the 12 or so tables for the projects so they all
have the number of rows specified in "C5" on the first workshet. I suspect
its a simple macro to write, but I have been unable to find even the correct
commands

How would the macro identify the 12 tables?

Where would the rows be added. At the top, in the middle, at the end.
Could you just redefine the extent of the table or do the rows have to be
inserted. Can whole rows be inserted or just cells in certain columns
(within the horizontal extent of the table). Are the rows just inserted or
do they need to have some data placed in them as well.

lNum = worksheets("Sheet1").Range("C5").Value

for each cell in mycollection
if cell.rows.count < lNum then
cell.offset(1,0)(1).Resize(lnum-cell.rows.count _
).EntireRow.Insert
end if
Next

--
Regards,
Tom Ogilvy




"Jonathan Oz" <Jonathan [email protected]> wrote in message
news:[email protected]...
> I am a neophyte to writing macros, but I think this may be a good
opportunity
> to learn. I have a template that I am creating for creating a simple
report.
> It consists of multiple worksheets with three or four tables per
worksheet.
> For a given project, I may need these tables to have 5 rows. For another,
I
> may need it to have 12 rows, and so on. I have created a worksheet where I
> enter the number of rows needed for the tables in a cell (say, "C5"). I
would
> like to create a macro that automatically goes through the worksheets and
> adds enough rows to each of the 12 or so tables for the projects so they
all
> have the number of rows specified in "C5" on the first workshet. I suspect
> its a simple macro to write, but I have been unable to find even the
correct
> commands

Tom

and I thought I had defined the problem fairly well. Oh well.

I frankly don't know how to get the system to recognize where the twelve
tables are. In opening the template, they will always be in the same places
when the macro is run. I suppose, I could name the range using Insert Name
from the menu bar.

I was thinking of adding them in the middle of the table in the hopes that
Excel would copy the formating that exists in the template.

The inserted rows may be treated as entire rows of the worksheet. There is
no need to shift down or shift right or anything like that.

The point of this insertion is to have a set of worksheets where data can be
entered without a lot of fussing about to ensure that the table has enough,
but not too many rows. In general, no data is actually involved. Although I
have a few places where formulas need to be placed in certain collumns.

Many Thanks


"Tom Ogilvy" wrote:

> How would the macro identify the 12 tables?
>
> Where would the rows be added. At the top, in the middle, at the end.
> Could you just redefine the extent of the table or do the rows have to be
> inserted. Can whole rows be inserted or just cells in certain columns
> (within the horizontal extent of the table). Are the rows just inserted or
> do they need to have some data placed in them as well.
>
> lNum = worksheets("Sheet1").Range("C5").Value
>
> for each cell in mycollection
> if cell.rows.count < lNum then
> cell.offset(1,0)(1).Resize(lnum-cell.rows.count _
> ).EntireRow.Insert
> end if
> Next
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "Jonathan Oz" <Jonathan [email protected]> wrote in message
> news:[email protected]...
> > I am a neophyte to writing macros, but I think this may be a good
> opportunity
> > to learn. I have a template that I am creating for creating a simple
> report.
> > It consists of multiple worksheets with three or four tables per
> worksheet.
> > For a given project, I may need these tables to have 5 rows. For another,
> I
> > may need it to have 12 rows, and so on. I have created a worksheet where I
> > enter the number of rows needed for the tables in a cell (say, "C5"). I
> would
> > like to create a macro that automatically goes through the worksheets and
> > adds enough rows to each of the 12 or so tables for the projects so they
> all
> > have the number of rows specified in "C5" on the first workshet. I suspect
> > its a simple macro to write, but I have been unable to find even the
> correct
> > commands
>
>
>