I have a macro that creates a number of tables on a daily basis.
These all have to be compiled into one report (Excel/Word).
How can I make so that at the correct places in my report the relevant table appears (the size of these table is not constant), and is shown in its entirity but does not overwrite anything below it.
Thanks

In which app are the tables created in?

and

which app is the preferred app for the report?

____________________________
Eggheads unite! You have nothing to lose but your yolks.

The tables are created in excel. I would prefer the report to be excel too, but it is not too important.
Cheers

Then Excel may be the best place to generate the report as well, depending on what other things are in your report. However, if you already have the report written, say in Word, you may want to experiment with the (nasty) prospect of inserting an Excel sheet into the Word doc at the point where the tables should appear.

To use Excel, I suggest you start by inserting a blank sheet in your workbook and begin writing the report (not the tables, just the other stuff)...leaving holes for your tables to go into. Once you generate the tables you need to copy those cells over to the holes you made.



____________________________
Eggheads unite! You have nothing to lose but your yolks.

That sounds like the way I have been doing it so far. The macro creates all my tables then I manually insert them in the gaps within my report. I have been unable to automate it though because the number of rows in all of the tables changes randomly each day. So copying the cells may one day write over text below or the next day leave empty rows.
Is there a way to place the table between two points in Excel and the report will expand by the length of the table?

Do you know the number of rows in your table?

If so you could repeat the following lines that number of times.

Rows("14:14").Select Selection.Insert Shift:=xlDown

No I don't know the number of rows for the tables, as they are random each day. However what you said has given me an idea:  I could do a count once thay are generated and then add that many rows afterwards. It will work, but I just wondered if there was a method of inserting data between rows rather than over them.
Thanks

no need to select the row first...

my1st_Table_Length = 55 For rw = 1 to my1st_Table_Length    Rows("14:14").Insert Next rw

Of course, there are several ways to go about what you're proposing. Inserting rows for your table implies that you will be deleting these rows later. In an effort to protect other rows that should not be deleted from mistakenly being deleted, I might consider inserting a large number of rows where your table will go, then inserting your table, then simply hiding the rows you don't use. These rows will never be deleted, just cleared when the report is refreshed...

[Edited by ~Bean~ on 10-06-2003 at 08:48 AM GMT]

____________________________
Eggheads unite! You have nothing to lose but your yolks.