AUTOMATIC way to copy the value of a cell in one spreadsheet

[Guest]

Hi,

I am trying to copy automatically the value from one cell in lets say
worksheet 1 to another cell in worksheet2. In order to do that I use the
‘Sheet1’!A1 function in the desired cell in Spreadsheet 2. This function
copies the value to the desired cell in Spreadsheet 2 though this value
doesn’t have the same format as the value in the cell of the Spreadsheet1.
For example a null value in Spreadsheet1 appears as 0 in Spreadsheet 2.

Is there any other function that I can use to perform this task? Do you know
any other AUTOMATIC way to copy the value of a cell in one spreadsheet to a
cell in another spreadsheet but keeping the same format?

Please help.

Thank in advance.

Regards
Michael

 

[Guest]

This might not be the best way to do it, but it is a work-around. Copy the
value from worksheet 1 and past special the value in worksheet #2. Then use
the paintbrush format and copy the format from workbook #1 to workbook #2.
This has worked for me in the past.

Hope it helps!

 

[vezerid]

Mihali,
The most automatic way to do this is with an event macro, specifically
the worksheet macro Change.

In your source sheet, Spreadsheet1, right-click the sheet tab and issue
View Code. This will take you to the VBA environment and specifically
to the code window for your source sheet.
Paste the following code, after suitable modifications:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("A1").Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlFormats
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlValues
Application.CutCopyMode = xlNone
End If
End Sub

Modify as necessary the values inside quotes.

HTH
Kostis Vezerides