I currently have a workbook with a macro to update a range from an identical range on a master sheet. Right now, I simply use a macro to open the master workbook, copy the range from it and paste the values in the same range in the other workbook.
Here is the code I am currently using:
Sub GetDataFromClosedWorkbook()
'Created by XXXX 5/2/2014
Application.ScreenUpdating = False ' turn off the screen updating
Dim wb As Workbook
Set wb = Workbooks.Open("LOCATION OF FILE", True, True)
' open the source workbook, read only
With ThisWorkbook.Worksheets("1")
' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
.Range("F8:K25").Value = wb.Worksheets("1").Range("F8:K25").Value
End With
With ThisWorkbook.Worksheets("2")
' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
.Range("V5:Z359").Value = wb.Worksheets("2").Range("V5:Z359").Value
End With
With ThisWorkbook.Worksheets("3")
' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
.Range("V5:AE238").Value = wb.Worksheets("3").Range("V5:AE238").Value
End With
With ThisWorkbook.Worksheets("4")
' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
.Range("V5:AB33").Value = wb.Worksheets("4").Range("V5:AB33").Value
End With
With ThisWorkbook.Worksheets("5")
' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
.Range("V5:Y140").Value = wb.Worksheets("5").Range("V5:Y140").Value
End With
With ThisWorkbook.Worksheets("6")
' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
.Range("V5:AD170").Value = wb.Worksheets("6").Range("V5:AD170").Value
End With
With ThisWorkbook.Worksheets("7")
' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
.Range("V5:AF579").Value = wb.Worksheets("7").Range("V5:AF579").Value
End With
With ThisWorkbook.Worksheets("8")
' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
.Range("Q5:AC182").Value = wb.Worksheets("8").Range("Q5:AC182").Value
End With
With ThisWorkbook.Worksheets("9")
' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
.Range("U5:AK120").Value = wb.Worksheets("9").Range("U5:AK120").Value
End With
With ThisWorkbook.Worksheets("10")
' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
.Range("V5:AC140").Value = wb.Worksheets("10").Range("V5:AC140").Value
End With
With ThisWorkbook.Worksheets("11")
' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
.Range("V5:AG947").Value = wb.Worksheets("11").Range("V5:AG947").Value
End With
With ThisWorkbook.Worksheets("12")
' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
.Range("V5:AB145").Value = wb.Worksheets("12").Range("V5:AB145").Value
End With
With ThisWorkbook.Worksheets("13")
' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
.Range("O5:AE10").Value = wb.Worksheets("13").Range("O5:AE10").Value
End With
With ThisWorkbook.Worksheets("14")
' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
.Range("V5:AA14").Value = wb.Worksheets("14").Range("V5:AA14").Value
End With
With ThisWorkbook.Worksheets("15")
' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
.Range("V5:AF201").Value = wb.Worksheets("15").Range("V5:AF201").Value
End With
With ThisWorkbook.Worksheets("16")
' read data from the source workbook: (Left of (=) is paste @ destination, right of it is copy)
.Range("Q5:AB14").Value = wb.Worksheets("16").Range("Q5:AB14").Value
End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub
As you can see, the code is very simple and updates a custom range where data lies in each of the 16 worksheets in the workbook.
The issue I'm running into is this: on the master workbook, the # of rows can change as I add data to it. Normally, I would get around this by just copying the entire worksheet and thus replicating the entire workbook each time it updates, the problem is that the workbooks being updated are saved by multiple people, who take notes/mark progress in a column in their workbooks. To help paint a better picture, imagine the (crude) matrix below is a worksheet. Each person's worksheet is getting a, b, c & d updated, but THEY enter in 1, 4, 2, etc. I need 1, 4, 2 etc to be preserved in each of their respective rows even as more rows of a, b, c & d are added. Right now, if I were to add a row of w, x, y & z above the 3rd row on the master, 4 would then become part of that row and i, j, k & l would be blank on the updated sheet.
a b c 1 d
e f g h
i j k 4 l
m n o p
q r s 2 t
u v w 6 x
Sorry for not making this clear earlier. You guys are right, linking workbooks is the right way to do it- the problem is that the server security here won't let that happen. Thus, I need to copy from an open workbook :(
See Question&Answers more detail:
os