VBA problem for forumla with relative reference
 

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

VBA problem for forumla with relative reference
 

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

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