Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 53
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 53
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Keeping forumla cell reference after cut and pasting Eli[_4_] Excel Discussion (Misc queries) 4 August 12th 08 01:15 AM
Copy & Paste Forumla - but reference cell is changing Andy Excel Discussion (Misc queries) 5 October 12th 07 04:41 PM
Using an offset formula for the reference in a relative reference Cuda Excel Worksheet Functions 6 November 15th 06 06:12 PM
Using cell reference and wildcards in a forumla steev_jd Excel Discussion (Misc queries) 3 July 11th 06 03:28 PM
Is there a forumla to link an absolute cell reference in multiple. Thomas Excel Worksheet Functions 2 February 5th 05 05:15 PM


All times are GMT +1. The time now is 12:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"