PDA

View Full Version : Referencing Cells in changing worksheets



BDRadio
07-12-2007, 07:57 AM
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.

Bob Phillips
07-12-2007, 08:07 AM
Change



Cells(n2, 3).Select
ActiveCell.FormulaR1C1 = "='1 Construction'!R[17]C[5]"


to



Cells(n2, 3).FormulaR1C1 = "='" & tabname & "'!R[17]C[5]"

BDRadio
07-12-2007, 11:11 AM
I am having a problem with this formula as when I put in the code:

ActiveCell.FormulaR1C1 = ref & "!F4"

it returns
='1_Construction'!'F4'

which as you see puts apostrophe's on each side of the !. Is there a way to code in an exclamation mark without apostrophe's around it?

Bob Phillips
07-12-2007, 12:16 PM
It doesn't do it for me, but maybe try



ActiveCell.FormulaA1 = ref & "!F4"