Ken,
It would be of benefit to you to learn some VBA for Excel, if it is permitted by your organization or you use it for your own purposes only. A word of warning, though. With the power to do great things also comes the power to do great harm. Always make sure you are backed up adequately before running a macro on important data, in case something goes wrong. Since you're fairly new to macros, I would recommend creating a simple scaled down version of your workbook to try the code I'm providing.
The areas on the main sheet where your tables belong, and the particular table which is to be copied into each area, must be specified in some way. I've used a scheme with named ranges which I think will be general enough to handle your needs. The tables will be numbered consecutively starting at 1, to allow convenient indexing by the code. Associated with each table will be three named ranges. For the first table on your main sheet, these will be TBL1, UL1, and LR1. Go to the worksheet which contains the first table, select its top left cell, then type TBL1 in the name box (just above column A). On the main sheet, select the cell which will contain the upper left cell of
the copied table, and enter UL1 (UL meaning "upper left") in the name box. This probably won't be immediately clear -- select the cell at the intersection of the first column and first row which must be shifted if required by table growth, and type LR1 (LR meaning "lower right") in the name box.
| insert_tables.xls |
|---|
|
|---|
| A | B | C | D | E | F | G | H |
|---|
| 1 | | | | | | | | |
|---|
| 2 | | X | X | | | | | |
|---|
| 3 | | X | X | | | | | |
|---|
| 4 | | | | | | | | |
|---|
| 5 | | | | | | | | |
|---|
| 6 | | | | | | | | |
|---|
| 7 | | | | | | | | |
|---|
|
|---|
In the diagram above, the bordered range is that currently allocated for Table 1 on the main worksheet. [EDIT: the cell borders didn't get through the HTML maker. The range is B2:D4]. Cell B2 (blue) is named UL1. The X's denote some data, so that the old Table 1 is 2 rows by 2 columns. Column E (yellow) and row 5 (green) are the first column and row which must be shifted if required by table growth. In this case, cell E5 (red) would be named LR1. When the macro is run, if the old table is to be replaced by an updated version with at most 3 columns, no columns will be inserted by the macro; similarly, if the updated version has at most 3 rows, no rows will be inserted. If the updated version has 4 rows and 5 columns, for example, then the macro will insert two columns at column E and 1 row at row 5, and copy the updated table so that the picture would then look like this:
| insert_tables.xls |
|---|
|
|---|
| A | B | C | D | E | F | G | H |
|---|
| 1 | | | | | | | | |
|---|
| 2 | | X | X | X | X | X | | |
|---|
| 3 | | X | X | X | X | X | | |
|---|
| 4 | | X | X | X | X | X | | |
|---|
| 5 | | X | X | X | X | X | | |
|---|
| 6 | | | | | | | | |
|---|
| 7 | | | | | | | | |
|---|
|
|---|
Note that after the shifts the red cell, now at G6, still retains the name LR1.
The next table and its desired location is defined in a similar way, with names TBL2, UL2, and LR2; and so on up to as many tables as needed.
To place the code below in your practice workbook, Hit Alt-F11, then Insert>Module, and then copy and paste the code into the new module. I've assumed that your main sheet is named "Main", so you may need to edit the line which I've identified with a comment. The constant iNumTables gives the number of tables the macro will update. Begin with a small value here, say 1 or 2, and see if the general idea seems workable for you. If so, then edit and change to a larger number for larger examples.
Note that one assumption I've made is that each source table consists of a single region -- i.e., there are no entirely blank columns or rows in the table.
I don't know if you want to consider the possibility of decreasing table sizes and possibly eliminating blank rows or columns after such an update. I'll wait on that until you finish your initial assessment of this scheme.
Code:
Sub UpdateTables()
Dim rNewTable As Range
Dim iLRRow As Integer, iLRCol As Integer
Dim iNumRows As Integer, iNumCols As Integer
Dim ws As Worksheet
Dim i As Integer, j As Integer
Const iNumTables As Integer = 1 'number of tables to update
'if necessary, change next line to use name of your main worksheet
Set ws = Sheets("Main")
For i = 1 To iNumTables
'clear out old table
Range(Range("UL" & i), Range("LR" & i).Offset(-1, -1)).ClearContents
iLRRow = Range("LR" & i).Row 'row number of lower right anchor
iLRCol = Range("LR" & i).Column 'column number of lower right anchor
'calculate number of rows and columns currently allocated for table
iNumRows = iLRRow - Range("UL" & i).Row
iNumCols = iLRCol - Range("UL" & i).Column
Set rNewTable = Range("TBL" & i).CurrentRegion 'updated table to be copied
'if necessary, insert new rows and/or columns to accommodate updated table
For j = 1 To rNewTable.Rows.Count - iNumRows
ws.Rows(iLRRow).Insert
Next
For j = 1 To rNewTable.Columns.Count - iNumCols
ws.Columns(iLRCol).Insert
Next
'copy new table to main sheet
rNewTable.Copy Destination:=Range("UL" & i)
Next
End Sub
Ron
