-1

This macro was written long before I was in my current role but now I want to improve it and make it work for any x number of funds. I am pasting just the part of the macro up to 4 funds but it continues the same way for up to 50 funds right now which you can imagine what a nightmare that is.

Edit: I should have mentioned, I intend to go through and remove all the Select and Activate commands where possible. This was done by someone on the Macro Recorder a few years ago so that is definitely a part of my clean-up but figuring out how to have the number of sheets copied and have the formulas work for any number of funds is what I am struggling with.

Sub MultiFunds()

MultiFund = InputBox("Enter the Total number of Funds. Up to 50.", "Up to Fifty Funds can be added on this form.")


' Copying Sheet
ActiveSheet.Unprotect
    Sheets("Fund 1").Copy Before:=Sheets(6)
    Sheets("Fund 1 (2)").Name = "Fund 2"
    
'Linking Cells Directs and IDC
    Sheets("Cumulative").Select
    Range("F15").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC"
    Range("F17").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC"
    Range("F15").AutoFill Destination:=Range("F15:K15"), Type:=xlFillDefault
    Range("F17").AutoFill Destination:=Range("F17:K17"), Type:=xlFillDefault
    
    Range("M15").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC"
    Range("M17").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC"
    Range("N15").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC"
    Range("N17").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC"

    
 'Linking Exclusions
    Range("F29").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC"
    Range("F29").AutoFill Destination:=Range("F29:K29"), Type:=xlFillDefault
    Range("F29:K29").AutoFill Destination:=Range("F29:k36"), Type:=xlFillDefault
    Range("M29").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC"
    Selection.AutoFill Destination:=Range("M29:m36"), Type:=xlFillDefault
    Range("N29").Select
    ActiveCell.FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC"
    Selection.AutoFill Destination:=Range("N29:n36"), Type:=xlFillDefault
    
    'Linking Dates
    Sheets("Fund 1").Select
    Range("F11").FormulaR1C1 = "=+Cumulative!RC"
    Range("F12").FormulaR1C1 = "=+Cumulative!RC"
    Range("F11").AutoFill Destination:=Range("F11:K11"), Type:=xlFillDefault
    Range("F12").AutoFill Destination:=Range("F12:K12"), Type:=xlFillDefault
    Range("F13").FormulaR1C1 = "=+Cumulative!RC"
    Range("F13").AutoFill Destination:=Range("F13:K13"), Type:=xlFillDefault
    Range("M13").FormulaR1C1 = "=+Cumulative!RC"
    Range("N13").FormulaR1C1 = "=+Cumulative!RC"


  
    ActiveCell.FormulaR1C1 = "=+Cumulative!RC"
    Range("C5").FormulaR1C1 = "=+Cumulative!RC"
    
    Sheets("Fund 1").Select
    Range("C3").FormulaR1C1 = "=+Cumulative!RC"
    Range("C5").FormulaR1C1 = "=+Cumulative!RC"

    
   ' Linkin Summary Sheet
    
    Sheets("Summary").Select
    ActiveSheet.Unprotect
    Range("A4").FormulaR1C1 = "=+'Fund 1'!R[-2]C[2]"
    Range("B4").FormulaR1C1 = "=+'Fund 1'!R[22]C[1]"
    Range("C4").FormulaR1C1 = "=+'Fund 1'!R[15]C[9]"
    Range("D4").FormulaR1C1 = "=+'Fund 1'!R[15]C[9]+'Fund 1'!R[15]C[10]"
    Range("E4").FormulaR1C1 = "=+RC[-2]+RC[-1]"
    Range("F4").FormulaR1C1 = "=+'Fund 1'!R[24]C[-3]"
    Range("G4").FormulaR1C1 = "=+RC[-5]-RC[-2]-RC[-1]"
    
    Range("A5").FormulaR1C1 = "=+'Fund 2'!R[-3]C[2]"
    Range("B5").FormulaR1C1 = "=+'Fund 2'!R[21]C[1]"
    Range("C5").FormulaR1C1 = "=+'Fund 2'!R[14]C[9]"
    Range("D5").FormulaR1C1 = "=+'Fund 2'!R[14]C[9]+'Fund 2'!R[14]C[10]"
    Range("E5").FormulaR1C1 = "=+RC[-2]+RC[-1]"
    Range("F5").FormulaR1C1 = "=+'Fund 2'!R[23]C[-3]"
    Range("G5").FormulaR1C1 = "=+RC[-5]-RC[-2]-RC[-1]"
    
    'Hide Rows
     Rows("6:33").EntireRow.Hidden = True
     
     Sheets("Summary").Select
     ActiveSheet.Protect
     Sheets("Cumulative").Select
     ActiveSheet.Protect
     Sheets("Fund 1").Select
     ActiveSheet.Protect
     Sheets("Fund 2").Select
     ActiveSheet.Protect
     
If MultiFund = 2 Then
End If

    If MultiFund = 3 Then

    Sheets("Cumulative").Select
    ActiveSheet.Unprotect
    Sheets("Fund 1").Copy Before:=Sheets(7)
    Sheets("Fund 1 (2)").Name = "Fund 3"
    
'LINKING DIRECT And INDIRECTs

    Sheets("Cumulative").Select
    Range("F15").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC+'Fund 3'!RC"
    Range("F17").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC+'Fund 3'!RC"
    Range("F15").AutoFill Destination:=Range("F15:K15"), Type:=xlFillDefault
    Range("F17").AutoFill Destination:=Range("F17:K17"), Type:=xlFillDefault
    Range("M15").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC+'Fund 3'!RC"
    Range("M17").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC+'Fund 3'!RC"
    Range("N15").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC+'Fund 3'!RC"
    Range("N17").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC+'Fund 3'!RC"
    
'LINKING EXCLUSIONS

    Range("F29").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC+'Fund 3'!RC"
    Range("F29").AutoFill Destination:=Range("F29:K29"), Type:=xlFillDefault
    Range("F29:K29").AutoFill Destination:=Range("F29:k36"), Type:=xlFillDefault
    Range("M29").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC+'Fund 3'!RC"
    Selection.AutoFill Destination:=Range("M29:m36"), Type:=xlFillDefault
    Range("N29").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC+'Fund 3'!RC"
    Selection.AutoFill Destination:=Range("N29:n36"), Type:=xlFillDefault
    
   
    'Linking Summary sheet
  
    
    Sheets("Summary").Select
    ActiveSheet.Unprotect
    Rows("6").EntireRow.Hidden = False
    
    Range("A6").FormulaR1C1 = "=+'Fund 3'!R[-4]C[2]"
    Range("B6").FormulaR1C1 = "=+'Fund 3'!R[20]C[1]"
    Range("C6").FormulaR1C1 = "=+'Fund 3'!R[13]C[9]"
    Range("D6").FormulaR1C1 = "=+'Fund 3'!R[13]C[9]+'Fund 3'!R[13]C[10]"
    Range("E6").FormulaR1C1 = "=+RC[-2]+RC[-1]"
    Range("F6").FormulaR1C1 = "=+'Fund 3'!R[22]C[-3]"
    Range("G6").FormulaR1C1 = "=+RC[-5]-RC[-2]-RC[-1]"
    
     Sheets("Summary").Protect
     Sheets("Cumulative").Protect
     Sheets("Fund 1").Protect
     Sheets("Fund 2").Protect
     Sheets("Fund 3").Protect

End If

If MultiFund = 4 Then

    Sheets("Cumulative").Unprotect
    
    Sheets("Fund 1").Copy Before:=Sheets(7)
    Sheets("Fund 1 (2)").Name = "Fund 3"
    Sheets("Fund 1").Copy Before:=Sheets(8)
    Sheets("Fund 1 (2)").Name = "Fund 4"

'LINKING DIRECT And INDIRECTs

    Sheets("Cumulative").Select
    Range("F15").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC+'Fund 3'!RC+'Fund 4'!RC"
    Range("F17").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC+'Fund 3'!RC+'Fund 4'!RC"
    Range("F15").AutoFill Destination:=Range("F15:K15"), Type:=xlFillDefault
    Range("F17").AutoFill Destination:=Range("F17:K17"), Type:=xlFillDefault
    Range("M15").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC+'Fund 3'!RC+'Fund 4'!RC"
    Range("M17").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC+'Fund 3'!RC+'Fund 4'!RC"
    Range("N15").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC+'Fund 3'!RC+'Fund 4'!RC"
    Range("N17").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC+'Fund 3'!RC+'Fund 4'!RC"
    
'LINKING EXCLUSIONS

    Range("F29").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC+'Fund 3'!RC+'Fund 4'!RC"
    Range("F29").AutoFill Destination:=Range("F29:K29"), Type:=xlFillDefault
    Range("F29:K29").AutoFill Destination:=Range("F29:K36"), Type:=xlFillDefault
    Range("M29").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC+'Fund 3'!RC+'Fund 4'!RC"
    Selection.AutoFill Destination:=Range("M29:m36"), Type:=xlFillDefault
    Range("N29").FormulaR1C1 = "=+'Fund 1'!RC+'Fund 2'!RC+'Fund 3'!RC+'Fund 4'!RC"
    Selection.AutoFill Destination:=Range("N29:n36"), Type:=xlFillDefault
      
 'Linking Summary Sheet
 
    Sheets("Summary").Unprotect
    Rows("6:7").EntireRow.Hidden = False
 
    
    Range("A6").FormulaR1C1 = "=+'Fund 3'!R[-4]C[2]"
    Range("B6").FormulaR1C1 = "=+'Fund 3'!R[20]C[1]"
    Range("C6").FormulaR1C1 = "=+'Fund 3'!R[13]C[9]"
    Range("D6").FormulaR1C1 = "=+'Fund 3'!R[13]C[9]+'Fund 3'!R[13]C[10]"
    Range("E6").FormulaR1C1 = "=+RC[-2]+RC[-1]"
    Range("F6").FormulaR1C1 = "=+'Fund 3'!R[22]C[-3]"
    Range("G6").FormulaR1C1 = "=+RC[-5]-RC[-2]-RC[-1]"
    
    Range("A7").FormulaR1C1 = "=+'Fund 4'!R[-5]C[2]"
    Range("B7").FormulaR1C1 = "=+'Fund 4'!R[19]C[1]"
    Range("C7").FormulaR1C1 = "=+'Fund 4'!R[12]C[9]"
    Range("D7").FormulaR1C1 = "=+'Fund 4'!R[12]C[9]+'Fund 4'!R[12]C[10]"
    Range("E7").FormulaR1C1 = "=+RC[-2]+RC[-1]"
    Range("F7").FormulaR1C1 = "=+'Fund 4'!R[21]C[-3]"
    Range("G7").FormulaR1C1 = "=+RC[-5]-RC[-2]-RC[-1]"
  
     Sheets("Summary").Protect
     Sheets("Cumulative").Protect
     Sheets("Fund 1").Protect
     Sheets("Fund 2").Protect
     Sheets("Fund 3").Protect
     Sheets("Fund 4").Protect

End If
End Sub

Any advice on how to change the code to create x number of copies as needed would be greatly appreciated!

Kevin
  • 25
  • 4
  • Start by reading https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – SJR Oct 20 '20 at 21:18
  • Oh I should have mentioned, I fully intended to go through and remove all the selects and activates where possible. It's more just being able to find a solution where I can create x number of tabs and have those formulas update as necessary that I was looking for. This was done on the macro recorder by someone a few years ago, hence all the selects and activates. – Kevin Oct 20 '20 at 21:24
  • 1
    I for one don't want to wade through reams of code to work out what is needed. Post an [mcve] and strip the code down as far as possible. Do that first and then do the other thing. – SJR Oct 20 '20 at 21:31
  • You're right, sorry for that. I think this is only my second or third time posting so I don't know all the best practices. I've stripped down the code to remove most of those Selects and think an example going up to 4 funds should be the minimum amount needed to see how the code continues, currently all the way up to 50 funds. – Kevin Oct 20 '20 at 22:00
  • No apology required. I’ll take a look tomorrow if it’s still unanswered. – SJR Oct 20 '20 at 22:18

1 Answers1

0

The technique to use is to create a Sub that is passed a variable for the number of sheets to add. Use the Input box to get the required number, validate it, then call the Sub

In the Sub, create loops that contain the required code, using the loop index to reference the required Cells/Worksheets etc.

Heres a starter. It's not a complete solution, but intended to give youe some techniques to use

Sub MultiFunds()
    Dim MultiFund As Variant
    Dim MaxFunds  As Long
    MaxFunds = 100
    ' Clarify:
    '    is MultiFund the number of Funds to add (named 2 to MultiFund + 1) or the total number of Funds?
    '    what if there are already more than 1 Fund in the book?
    MultiFund = Application.InputBox("Enter the Total number of Funds to add.  Maximum of " & MaxFunds, "Create Funds Sheets", 0, , , , , 1)
    If MultiFund <> False Then
        If MultiFund > 0 And MultiFund <= MaxFunds Then
            ' Adjust sheet references to suit your layout
            ' alternatively, specify the workbook you want or use ActiveWorkbook
            AddFundSheets _
              wsBase:=ThisWorkbook.Worksheets("Fund 1"), _
              wsSummary:=ThisWorkbook.Worksheets("Summary"), _
              wsCopyLocation:=ThisWorkbook.Worksheets("Fund 1"), _
              wsCumlative:=ThisWorkbook.Worksheets("Cumulative"), _
              Funds:=CLng(MultiFund), _
              FundBaseName:="Fund "
        End If
    End If

End Sub

Sub AddFundSheets(wsBase As Worksheet, wsSummary As Worksheet, wsCopyLocation As Worksheet, wsCumlative As Worksheet, Funds As Long, FundBaseName As String)
    Dim wsFund As Worksheet
    Dim f As String
    Dim i As Long
    Dim InsertAfter As Long
    
    ' Add all your cleaned up code here, looping through the required number of funds
    
    'Example
    'Add Sheets, named FundBaseName & 2 to FundBaseName & Funds + 1
    ' this assumes no sheets are hidden.  If ther are Hidden sheets this may not work as expected
    InsertAfter = wsCopyLocation.Index
    For i = 2 To Funds + 1
        wsBase.Copy After:=wsCopyLocation.Parent.Worksheets(InsertAfter)
        wsCopyLocation.Parent.Worksheets(InsertAfter + 1).Name = FundBaseName & i
        InsertAfter = InsertAfter + 1
    Next
    
    'Example
    'Cumulative formula
    f = vbNullString
    For i = 1 To Funds + 1
        f = f & "+'" & FundBaseName & i & "'!RC"
    Next
    wsCumlative.Range("F15:K15").FormulaR1C1 = "=" & f
    
    
    ' Example
    'Add formulas to Summary sheet
    For i = 1 To Funds + 1
        wsSummary.Cells(i + 3, 1).FormulaR1C1 = "='" & FundBaseName & i & "'!R[-2]C[2]"
        wsSummary.Cells(i + 3, 2).FormulaR1C1 = "='" & FundBaseName & i & "'!R[22]C[1]"
        wsSummary.Cells(i + 3, 3).FormulaR1C1 = "='" & FundBaseName & i & "'!R[15]C[9]"
        wsSummary.Cells(i + 3, 4).FormulaR1C1 = "='" & FundBaseName & i & "'!R[15]C[9]+'" & FundBaseName & i & "'!R[15]C[10]"
        wsSummary.Cells(i + 3, 5).FormulaR1C1 = "=+RC[-2]+RC[-1]"
        wsSummary.Cells(i + 3, 6).FormulaR1C1 = "='" & FundBaseName & i & "'!R[24]C[-3]"
        wsSummary.Cells(i + 3, 7).FormulaR1C1 = "=+RC[-5]-RC[-2]-RC[-1]"
    Next
    
    
End Sub

Note: there is a lot of code in your OP, which I haven't tried to untangle (typical of Macro Recorder code). You'll need to work on that.

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • This is very helpful, thank you! One question that comes up for me here is that you'll notice in the code I posted that the R values change by 1 with each additional fund. If you scroll to the bottom of the code and look at how it sets the formulas for fund 3 vs fund 4 you will see the R value off by 1 with each additional fund. Is there a way to account for that? Again, very helpful and thanks so much for your time! – Kevin Oct 21 '20 at 15:27
  • Generally, use the loop index to calculate the value required. Eg `... &"R[" & (-i - 1) & "]" & ...` to get -2, -3, etc – chris neilsen Oct 21 '20 at 18:39