Copied new sheet refresh link to master sheet

Hi everybody!

I am quiete new at VBA. I am tring to make a code for refresh link in a mater sheet if I copy a definied sheet.

I have 20 sheets called V001, V002, V003 etc... I also have a sheet called MasterV. Every subsheets V001, V002.. to the master sheet.
For example MasterV B6 cell='V001'!A6+ 'V002'!A6+.... So what I need if I make a new sheet called V021 which is hadnt been exsist before refresh the link in MasterV for this = 'V001'!A6+ 'V002'!A6+....+'V021'!A6

Is there any possibility for this? Or is it too complicated for VBA?
Thank you so much!

You could use something like:

Please Login or Register  to view this content.

So what I have now:

Sub RefreshLinkDetail()
'
' Refresh links in building detail WITH THE NEW TAB
Range("C5").Select
ActiveCell.FormulaR1C1 = _
"='V001'!RC[6]+'V002'!RC[6]+'V003'!RC[6]+'V004'!RC[6]+'V005'!RC[6]+'V006'!RC[6]+'V007'!RC[6]+'V008'!RC[6]+'V009'!RC[6]+'V010'!RC[6]+'V011'!RC[6]+'V012'!RC[6]+'V013'!RC[6]+'V014'!RC[6]+'V015'!RC[6]+'V016'!RC[6]+'V017'!RC[6]+'V018'!RC[6]+'V019'!RC[6]+AND HERE I NEED A FORMULA IF I MAKE A NEW SHEET CALLED V020 UPTDATE THE LINK, AFTER THAT IF I MAKE V021 UPDATE THAT ALSO"
Range("E5").Select
ActiveCell.FormulaR1C1 = _
"='V001'!RC[6]+'V002'!RC[6]+'V003'!RC[6]+'V004'!RC[6]+'V0055'!RC[6]+'V006'!RC[6]+'V007'!RC[6]+'V008'!RC[6]+'V009'!RC[6]+'V010'!RC[6]+'V011'!RC[6]+'V012'!RC[6]+'V013'!RC[6]+'V014'!RC[6]+'V015'!RC[6]+'V016'!RC[6]+'V017'!RC[6]+'V018'!RC[6]+'V019'!RC[6]"
Range("G5").Select
ActiveCell.FormulaR1C1 = _
"='V001'!RC[6]+'V002'!RC[6]+'V003'!RC[6]+'V004'!RC[6]+'V0055'!RC[6]+'V006'!RC[6]+'V007'!RC[6]+'V008'!RC[6]+'V009'!RC[6]+'V010'!RC[6]+'V011'!RC[6]+'V012'!RC[6]+'V013'!RC[6]+'V014'!RC[6]+'V015'!RC[6]+'V016'!RC[6]+'V017'!RC[6]+'V018'!RC[6]+'V019'!RC[6]"
Range("B5:W5").Select
Selection.AutoFill Destination:=Range("B5:W88"), Type:=xlFillValues
Range("B5:W88").Select
End Sub

Any idea?

If you want to do it in that way I guess you could use a loop:

Please Login or Register  to view this content.

There is some problem with this line:
if strings.left(sheets(count).name,2) = "V0" and not sheets(count).name = "V000"

Sorry! it needs then on the end:
if strings.left(sheets(count).name,2) = "V0" and not sheets(count).name = "V000" then

Okey, now I have this formula:

Sub RefreshLink()
Sheets("Bulding detail backup contract").Activate
Range("C5").Select
ActiveCell = ""
For Count = 1 To Worksheets.Count
If Strings.Left(Sheets(Count).Name, 2) = "V0" And Not Sheets(Count).Name = "V000" Then
If ActiveCell = "" Then
ActiveCell.FormulaR1C1 = "='" & Sheets(Count).Name & "'!RC[6]"
Else
ActiveCell.FormulaR1C1 = ActiveCell.Formula & "+'" & Sheets(Count).Name & "'!RC[6]"
End If
End If
Next
End Sub

But i got runtime 13 error code! The result was at the C5 cell = 'V001'!'I5'+'V002'!I5

Do I have to insert this code to the sheet code? And I also want to repeat this code in PLs help!

Ah of course, you can't build a R1C1 type formula in that way, you would need to first set it to a string and then to the cell formula:

Please Login or Register  to view this content.

I still think you should use the first suggestion and update the formulas when you add the new sheet, rather than having a separate procedure with the full formulas but this should work.

It is working! Nice :D

May I have one more question? U are really good at this and I am really suffering to do this code So how can I make to repeat this formula in the E5, G5, I5, K5, M5, O5, Q5, S5 cells? (Every second cell until w5)?

Please Login or Register  to view this content.

Thanks so much!!! I am really happy now :D