Macro for Adding Lines to Linked Spreadsheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
Back
Top