Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping forumla cell reference after cut and pasting | Excel Discussion (Misc queries) | |||
Copy & Paste Forumla - but reference cell is changing | Excel Discussion (Misc queries) | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Using cell reference and wildcards in a forumla | Excel Discussion (Misc queries) | |||
Is there a forumla to link an absolute cell reference in multiple. | Excel Worksheet Functions |