Hi guys,
I'm relatively new to VBA coding. I am using Office XP and here is what I am trying to do. I am building a pricing model which adds a new fund when a button is pushed. It creates a new sheet based on the users input into the input box. At the end of the macro I need to make reference to specific cells on a summary page from the pricing tab which it just created. I know the code may be sloppy but if you see the highlighted part near the end, I cannot figure out how to reference a tab as it's name will be different everytime the macro is run. The name of the sheet is the string labeled tabname. Is there a way to use the formulaR1C1 command to incorporate a defined string plus a range to reference a different cell?
__________________________
Sub NewFund()
'
' NewFund Macro
' Creates a new fund
'
Dim n As Integer
Dim n2 As Integer
Dim fund As String
Dim tabname As String
'Run input box
fund = InputBox("Enter The Fund Name", "Fund Type")
If fund = "" Then
Exit Sub
Else
End If
'Define Cells & Tab
n = Range("A1")
n2 = n + 1
tabname = Cells(n, 1) & " " & fund
'insert new sheet for fund template
Sheets.Add
ActiveSheet.Name = tabname
'enter info in summary
Sheets("Summary").Select
Range(Cells(n, 1), Cells(n2, 11)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'Distinguishes Bid Specs from Underwriting
Range(Cells(n, 2), Cells(n, 11)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
'Enters Title Names
Cells(n, 4).Select
ActiveCell.FormulaR1C1 = "Bid Specs"
Cells(n2, 4).Select
ActiveCell.FormulaR1C1 = "Underwriting"
Cells(n, 2).Select
ActiveCell.FormulaR1C1 = fund
Cells(n2, 2).Select
ActiveCell.FormulaR1C1 = "Fee:"
With Selection
.HorizontalAlignment = xlRight
End With
Sheets("Template").Select
Cells.Select
Selection.Copy
Sheets(tabname).Select
Cells.Select
ActiveSheet.Paste
ActiveWindow.Zoom = 85
'Creates a link to new worksheet cells
Sheets("Summary").Select
Cells(n2, 3).Select
ActiveCell.FormulaR1C1 = "='1 Construction'!R[17]C[5]"
Cells(n, 3).Select
ActiveCell.FormulaR1C1 = "='1 Construction'!R[14]C[5]"
Cells(n2, 5).Select
ActiveCell.FormulaR1C1 = "='1 Construction'!R[12]C[5]"
Cells(n, 5).Select
ActiveCell.FormulaR1C1 = "='1 Construction'!R[13]C[3]"
Cells(n, 6).Select
ActiveCell.FormulaR1C1 = "='1 Construction'!R[9]C[2]"
Cells(n2, 6).Select
ActiveCell.FormulaR1C1 = "='1 Construction'!R[8]C[4]"
Cells(n, 7).Select
ActiveCell.FormulaR1C1 = "='1 Construction'!R[9]C[2]"
Cells(n2, 7).Select
ActiveCell.FormulaR1C1 = "='1 Construction'!R[8]C[4]"
Cells(n, 8).Select
ActiveCell.FormulaR1C1 = "='1 Construction'!R[6]C[1]"
Cells(n2, 8).Select
ActiveCell.FormulaR1C1 = "='1 Construction'!R[5]C[3]"
Cells(n, 9).Select
ActiveCell.FormulaR1C1 = "='1 Construction'!R[4]C"
Cells(n2, 9).Select
ActiveCell.FormulaR1C1 = "='1 Construction'!R[3]C[2]"
Range("I8").Select
End Sub
_________________________________________________
I would like those in bold to equal Sheets(tabname).Range("C15) (or whatever cells i need), but i need it to be equal to the cell from the referenced sheet and not just copied as the data is actively changing.
Reply With Quote
