Help with duplicating workbooks

[styphon]

I am completely new to the world of coding excel. I do some basic VBA coding in Access but never in excel. I have a problem where certain excel documents with 1 workbook each are kept in seperate locations with restricted access. I also have a central document with a copy of all the workbooks. I want it so if I update a cell on one workbook, that cell copies the data to the master document. I know I can do

='[FILE NAME.xls]WORKBOOK'!$A$1

but that means I have to do this for every cell for every row. Each workbook will have hundreds of records so this isn't really an option. Is there a way to automate the process? I would like to copy Cells A1:G100 first time, then A101:G200 the next and so on. Or even better would be to do the whole workbook in one hit.

 

[qafself]

Is the layout the same in the destination workbook as the source? If so once you have got your first cell entered, remove the dollar signs and then by right clicking and holding on the little + sign on the bottom RHS you can copy over the range you want.

HTH

Ed

 

[styphon]

Is the layout the same in the destination workbook as the source? If so once you have got your first cell entered, remove the dollar signs and then by right clicking and holding on the little + sign on the bottom RHS you can copy over the range you want.

HTH

Ed

THANK YOU!!! I didn't even know you could remove the $ signs. You have just helped me no end.

 

[qafself]

Glad to have helped and thanks for the feedback.

If you select a cell reference in the formula bar, press F4 and it will cycle through the possibilities by repeated F4 pressing.

Ed

 

[styphon]

Thanks, interesting to know. I now have one very happy customer. Thanks again.