Cell in new sheet should always refer to defined cell in another s

[Ronnie]

Hi,

How can I make a cell on a worksheet always accept input from a certain cell
on any new copy of another worksheet?

In Excel 2003 I have made a workbook which I could use as a template, but I
would like to just have one book for multiple transactions, each on a newly
created sheet.

Base figures and calculations are on two sheets. One is protected with the
user allwed to modify one unlocked cell only. The other is protected with no
edits allowed. These sheets accept information from and display results on a
third sheet.

The third sheet is also protected, but the user can enter a variety of
required information in certain cells, which pass the user input back to the
two sheets containing the base calculations and formulae, and obtains the
results from them. Results are displayed in non editable cells. I intend
this sheet to be a template sheet so that using the Move or Copy context menu
the user can create and name a new sheet for a new transaction. The
information is passed about by Copy > Paste Special > Paste Link.

The problem is that when I make a new copy of the third woksheet, the input
is not passed back to the base sheets, as those sheets only reference the
original third sheet.

I have looked at 3d references, but a predetermined number of worksheets
must exist for that to work.

I'd be grateful for any advice on this.

Regards

 

[BSc Chem Eng Rick]

Hi Ronnie

The only way I can think to do this requires a little VBA. Basically
wherever you need to reference your "third" sheet in the first or second
sheets you use the "Activesheet" and "Range" properties to put the correct
numbers in their respective places.

Here's my simple example. I have two workbooks called "MyWB1.xls" and
"MyWB2.xls". In MyWB1 I want cell A1 to have the same value as A1 in MyWB2
and I want cell A3 to have the same value as A2 in MyWB2. So here's the code
that is written to MyWB2 (which would be your "third" sheet).

Sub UpdateValues()
With Workbooks("MyWB1.xlsx").Worksheets(1)
.Range("A1").Value = ThisWorkbook.ActiveSheet.Range("A1").Value
.Range("A3").Value = ThisWorkbook.ActiveSheet.Range("A2").Value
End With
End Sub

Now all you do on your third sheet is insert a shape (anything you like),
right click and then "Assign Macro". You assign the above macro. Now when
someone copies this sheet, they copy the button as well. They can modify the
values and then simply click the button to do the calculation.

If this is helpful click yes.
Rick

 

[Ronnie]

Thank you Rick,

That is very helpfull and seems to be exactly what I want to do.

However I get a "Compile Error: Expected: List Seperator or )" when I code
the following:

Sub UpdateValues()
With Workbooks("TEST VB Price Analytics 10pc.xls").Worksheets(Base
Figures)
.Range("G3").Value = ThisWorkbook.ActiveSheet.Range("B19").Value
.Range("I3").Value = ThisWorkbook.ActiveSheet.Range("B31").Value
End With
End Sub

Where the Workbook is called 'TEST VB Price Analytics 10pc.xls', The Sheet I
want the Macro on (my third sheet) is 'Form' and the sheet where the
calculations are done (my first sheet) is Base Figures.

If I save the Macro as it stands, with errors, when I click the Shape on the
worksheet 'Form' it reoprts 'The macro "TEST VB Price Analytics
10pc.xls'!Picture3_Click' cnnot be found.

Can you see at a glance what I am doing wrong?

Thank you for your help - I am a complete newbie at Macros and VB.

Regards

Ronnie.

 

[BSc Chem Eng Rick]

Glad I could help.

The compile error is being generated because you haven't got the worksheet
name enclosed in double quotes as follows: Worksheets("Base Figures"). That
should sort both problems out.

 

[Ronnie]

Thank you once again Rick!

I've corrected that, and also found that I needed to assign the macro as
'UpdateValues' to the image in order for the workbook to find it.

However I now get a Run-Time Error '1004' , which 'Help' does not help with.
The debugger stops at:

..Range("G3").Value = ThisWorkbook.ActiveSheet.Range("B19").Value

so I assume that there is something wrong with my statements or arguments on
this and the following line.

I'd be grateful for your further thoughts!

Regards

Ronnie.

 

[BSc Chem Eng Rick]

No surprise with help not helping
You mentioned your sheets were protected, this may be preventing the macro
from writing to the protected cells. Just try and unprotect them and see if
it at least allows you to run the macro because it runs ok on my side.

 

[Ronnie]

Rick that is brilliant! Thank you very much.

I have unlocked just the cells I need to update, and protected the sheet
allowing users to select unlocked cells. It works perfectly now, and I can
create the new sheets as intended.

I really should have thought of the protection issue myself, but many thanks
for your expert help.

Best Wishes

Ronnie.

 

[BSc Chem Eng Rick]

My pleasure.

And just in case you are interested you can protect and unprotect your
worksheets in the macro itself. But obviously be careful of this because as
you will see below you need to include the password written out. Here is a
macro which unprotects "Sheet1" then calls our UpdateValues macro and when
that's finished it Protects the workbook again with the same password.

Sub SheetUnlock()
Workbooks("MyWB").Worksheets("Sheet1").Unprotect("MyPassword")
UpdateValues
Workbooks("MyWB").Worksheets("Sheet1").Protect("MyPassword")
End Sub

 

[Ronnie]

Now that is very cool!

I'll save it and try it out.

Many thanks.