PDA

View Full Version : Link spreadsheets



Tira
12-11-2017, 01:46 PM
Please help me link each tab to the first tab/worksheet in the attached workbook. The tabs with -Bank on it should be linked to the first table of the spreadsheet and the other tabs without a "-Bank" gets link to the second table in the spreadsheet.

offthelip
12-12-2017, 03:12 AM
Here you are: these two subroutines will do it for you, However I had to change the names for the novitas columns because they don't match the names for the tabs.
So change M6 to "JL - NOVITAS"
change AF6 and AG6 to "NOVITAS"
then run these subs:


Sub writeformula()Worksheets("Volume Summary").Select
inarr = Range(Cells(1, 5), Cells(84, 16))
For i = 9 To 84
For j = 1 To 12
inarr(i, j) = "='" & inarr(6, j) & "'!D" & i
Next j
Next i
Range(Cells(1, 5), Cells(84, 16)) = inarr
End Sub


Sub writeformulabanks()
Worksheets("Volume Summary").Select
inarr = Range(Cells(1, 24), Cells(84, 36))
For i = 9 To 84
For j = 1 To 13
inarr(i, j) = "='" & inarr(6, j) & "-Bank'!D" & i
Next j
Next i
Range(Cells(1, 24), Cells(84, 36)) = inarr
End Sub