Macro for Adding Lines to Linked Spreadsheets

[Guest]

I need to create a macro for adding additional rows from one linked
spreadsheet to another.
I have two workbooks that have spreadsheets that are linked.
Workbook 1: which has spreadsheets with rows of material added daily.
Workbook 2: which is linked into workbook 1 and "copies" those sheets
exactly through formulas in the cells provided by the link.
However, I have to manually add the new rows in workbook 2 to accomodate the
new row material added in workbook 1. Is there a macro formula to add the
additional lines and carry the formulas down? I'm trying to save some steps
in making sure the linked spreadsheets are cohesive.

 

[macropod]

Hi Irishimp23,

If your linking is in the form of a simple
='[ExternalWorkbook.xls]WorkSheetName'!A1
formula, you could change this to:
=IF('[ExternalWorkbook.xls]WorkSheetName'!A1="","",='[ExternalWorkbook.xls]WorkSheetName'!A1)
and copy down & across at least as far as you're likely to need.
If you need to allow for rows/columns being added or deleted in the middle of the range, you could change this to
=IF(OFFSET('[ExternalWorkbook.xls]WorkSheetName'!$A$1,ROW()-1,COLUMN()-1)="","",OFFSET('[ExternalWorkbook.xls]WorkSheetName'!$A$1,ROW()-1,COLUMN()-1))
and copy down & across at least as far as you're likely to need.

The only things you need to watch with either approach is that (a) if you add new rows/columns to the source workbook, your formulae
in the target workbook still cover sufficent cells and (b) the results won't carrying over any consequntial formatting changes.

Cheers