First off, go easy I'm a novice. I need to reference different types of cells (ie. strings, doubles) from a template workbook that I create and save as multiple workbooks. I have figured out how to get the value or string to transfer to my master file but I want to be able to open one of the workbooks and change a value and hence have the master file change too. Also, the master workbook is saved in a different location than the other workbooks. Any help would be appreciated.
Thanks
Sub HyperLink()
'This macro creates a new excel workbook based on the selected part number of the master file.
'It also fills in three cells in the newly created excel file based on the master.
'Certain cells in the newly created workbook are referenced back to the master
'Finally the part number in the master excel is hyperlinked to the newly created excel
Dim PartNumber As String
Dim EAU As Integer
Dim CurrentMaterial As Double
Dim SimForging As String
Dim EstForging As String
Dim ProcessSavings As String
ColumnLocation = ActiveCell.Column
CellLocation = ActiveCell.Address
PartNumber = Cells(ActiveCell.Row, ActiveCell.Column) 'Defines the part number for the active cell
EAU = ActiveCell.Offset(0, 1).Value 'Defines the EAU for the selected part number (column C)
CurrentMaterial = ActiveCell.Offset(0, 2).Value 'Defines the Mat. Cost for the selected part number (column D)
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
If IsNull(PartNumber) = False Then
Workbooks.Add
FilePath = "C:\Documents and Settings\Brandon's\Desktop\Forging Justification\" & PartNumber & ".xlsx"
Workbooks.Open Filename:="C:\Documents and Settings\Brandon's\Desktop\Forging Justification\Template.xls"
Range("A2").Select
ActiveWorkbook.SaveAs Filename:=FilePath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Range("A2").Select
ActiveCell.Value = PartNumber
ActiveCell.Offset(4, 0).Value = EAU
ActiveCell.Offset(0, 3).Value = CCur(CurrentMaterial)
'I dont know what to use here?????????????????????
SimForging = Range("A8").FormulaR1C1
ProcessSavings = Range("C11").FormulaR1C1
EstForging = Range("F2").FormulaR1C1
ActiveWorkbook.Save
ActiveWindow.Close
ActiveWindow.Close
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="Pricing\" & PartNumber & ".xlsx", TextToDisplay:=PartNumber
'Again I don't know what to do here in order to make it dynamic????????????
'I want to be able to change the EstForging in PartNumber.xlsx and have it update the master file
ActiveCell.Offset(0, 4) = EstForging
ActiveCell.Offset(0, 5) = SimForging
ActiveCell.Offset(0, 7) = ProcessSavings
ActiveWorkbook.Save
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End If
End Sub
Siddarth,
Thanks for the response, it should work like this.
Choose the cell (a PartNumber ie. 1234) I want to run the macro for and start macro
1) Define PartNumber, EAU and CurrentMaterial
PartNumber = Cells(ActiveCell.Row, ActiveCell.Column)
EAU = ActiveCell.Offset(0, 1).Value
CurrentMaterial = ActiveCell.Offset(0, 2).Value
2) Open a template I created. Workbooks.Open Filename:="X:\New Parts - Miller, Crum\Forging Justification\Template.xls"
3) Save the Template.xls as a new file (ie. 1234.xlsx)
"C:\Documents and Settings\Brandon's\Desktop\Forging Justification\" & PartNumber & ".xlsx"
4) Place the PartNumber, EAU and CurrentMaterial into the 1234.xlsx
Range("A2").Select
ActiveCell.Value = PartNumber
ActiveCell.Offset(3, 0).Value = EAU
ActiveCell.Offset(0, 3).Value = CCur(CurrentMaterial)
5) Define SimForging, EstForging and ProcessSavings in the 1234.xlsx
SimForging = ActiveCell.Offset( , )
EstForging = ActiveCell.Offset( , )
ProcessSavings = ActiveCell.Offset( , )
6) Save 1234.xlsx and close it.
7) With the master file active again, place Simforging,EstForging and ProcessSavings in the same row as the PartNumber
ActiveCell.Offset( , ) = SimForging
ActiveCell.Offset( , ) = EstForging
ActiveCell.Offset( , ) = ProcessSavings
8) Add a hyperlink to the PartNumber cell to the file I just created (1234.xlsx)
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="Pricing\" & PartNumber & ".xlsx",_ TextToDisplay:=PartNumber
9) Save the master workbook and stop macro.
I then want to be able to hit the hyperlink and open 1234.xlsx.
Then change the ProcessSavings inside of 1234.xlsx, save and close it.
I want to be able to see the changes in the master workbook.
Right now I have only been able to bring the values across from 1234.xlsx but when I change them, it does not update in the master file???? EstForging and ProcessSavings are doubles while SimForging is a string.
