VBA problem for forumla with relative reference
Great works a treat, thanks
"Dave Peterson" wrote:
Dim TemplateWks As Worksheet
Dim NewWks As Worksheet
Dim myCell As Range
Dim myRng As Range
Set TemplateWks = Worksheets("worksheet 2")
Set myRng = Selection
For Each myCell In myRng.Cells
TemplateWks.Copy _
after:=Worksheets(Worksheets.Count)
Set NewWks = ActiveSheet
NewWks.Name = myCell.Value
'the value???
NewWks.Range("I5").Value = myCell.Offset(0, 1).Value
'or a formula that points back to that cell???
NewWks.Range("I5").Formula _
= "=" & myCell.Offset(0, 1).Address(external:=True)
Next myCell
Joanne wrote:
I am creating a macro that so far works great for inserting new worksheets
(formatting copied from worksheet 2) for a selection found on worksheet 1.
The problems comes when I'm trying to create a formula for the same cell (I5)
in each new worksheet to equal a specific cell in the same row as the one
originally selected.
To demonstrate
Worksheet 1
A B
Product 1 12
Product 2 45
Product 3 87
The macro so far creates a new worksheet for each product ie Product 1,
Product 2, Product 3, but then I want I5 to equal the value found in the B
column for each product.
The code so far is below. The ....... shows where I'm stuck!
For Each cell In Selection
Sheets("Worksheet 2").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
Range("I5").Select
ActiveSheet.FormulaR1C1 = "=Worksheet 1!......"
Next cell
Thanks in advance
--
Dave Peterson
|