Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro for Adding Lines to Linked Spreadsheets
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro for Adding Lines to Linked Spreadsheets
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 -- macropod [MVP - Microsoft Word] ------------------------- "Irishimp23" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Connecting Linked Spreadsheets | Excel Discussion (Misc queries) | |||
Adding rows in linked spreadsheets | Excel Discussion (Misc queries) | |||
linked spreadsheets | New Users to Excel | |||
Linked Excel Spreadsheets | Excel Discussion (Misc queries) | |||
Linked Excel Spreadsheets | Links and Linking in Excel |