Automatticly copy values only

[miicker]

Hello everyone,

I have a question, how can I automaticly Copy values from A1:A50 to B1:B50 (values only)

So I have a form like this:

A1 (2-1)
A2 (3-1)
etc..

And I want:
B1 1
B2 2
etc..

So I can hide column A and users will not see any formula.

Kind regards,
Miicker

 

[skywriter]

You have the option under cell formatting protection to hide the formulas.

 

[miicker]

Yeah I know, but I try to use the data in conditional formatting, so hiding the formula does not work, when I use that cell as source for conditional formatting I get an error, So I would like to automatticly copy the values of the cells.

 

[skywriter]

So A1 has the formula =2-1?
What is the conditional formatting you are using for this cell?

 

[miicker]

no its just a example, there is a IF and VLOOKUP in the cell A1, but I just want to copy the values only to B1, and when A1 changes, I want the value only data of B1 to change automatticly, is this possible?

 

[Michael M]

You could use ....copy>>Edit>>Paste Special>>Values only
but it won't change when A1 does !!

 

[miicker]

You could use ....copy>>Edit>>Paste Special>>Values only
but it won't change when A1 does !!

Yeah I know, thats why I need another solution, perhaps with VBA

 

[skywriter]

You can try this.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim tRange As Range
Set tRange = Range(ActiveCell.Address)

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Range("A1:A150").Copy
Range("B1:B150").PasteSpecial Paste:=xlPasteValues
tRange.Offset(1, 0).Select


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True




End Sub

Instructions for Installing Worksheet Code
1) Make sure the sheet you want to use this code on is active.
2) Right click the tab and choose view code.
3) Paste the code where the cursor is flashing.
4) Close the window.
5) Save the workbook.
6) The code will run when the event the code was written for is triggered.

 

[Michael M]

Maybe this then
Paste it into the Sheet Module

Private Sub Worksheet_calculate()
Columns("A:A").Copy
Columns("B:B").PasteSpecial Paste:=xlPasteValues
Columns("A:A").Hidden = True
End Sub

But it's simply a case of unhiding column"A" to see the formulae

 

[miicker]

You can try this.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim tRange As Range
Set tRange = Range(ActiveCell.Address)

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Range("A1:A150").Copy
Range("B1:B150").PasteSpecial Paste:=xlPasteValues
tRange.Offset(1, 0).Select


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True




End Sub

Instructions for Installing Worksheet Code
1) Make sure the sheet you want to use this code on is active.
2) Right click the tab and choose view code.
3) Paste the code where the cursor is flashing.
4) Close the window.
5) Save the workbook.
6) The code will run when the event the code was written for is triggered.

Thank you, this is exactly what I needed.