Auto Copy Cell Value from Cell with Formula

[sapter5361]

I'm trying to have the value of cell 'A1' automatically copied to cell 'B1', whereby cell 'A1' is a formula cell. I know that I can do this by copy/paste-special, but since this is a very repetitive task, I'd like to have Excel do it automatically for me.

Please help me.

I'm sure the solution is in one of the posts on this forum, but after spending quite some time searching here and elsewhere, I can't seem to find it. I'm a novice at this and most of the other solutions are for more complicated problems involving entire ranges and multiple nested IFs and vlookups.

 

[Ralajer]

Welcome to the board sapter5361.

Could you elaborate on what is meant by automatically. You can record a macro of the copy and paste operations and it can be called by running the macro manually, when the a cell changes or several other ways. Just a little more detail would help.

 

[sapter5361]

Thank you for taking the time to address my question.

I have a range of 10 cells (C1:C10) with data in them. Then there's 'A1' which has a formula that calculates the contents of C1:C10. I'd like 'B1' to automatically get the 'value' of 'A1', as 'A1' changes. The value of 'A1' changes hundreds of times a day as a user inputs and changes data in range C1:C10.

If a macro can indeed be written to automatically update 'B1' with the value of 'A1', as it changes, I'd be very happy.

Many thanks!

 

[Ralajer]

If you place this code in the Worksheet code module it will do what your want.

I might be missing something but I this will always have the same value in B1 and A1 if A1 is solely dependent on C1:C10

Private Sub Worksheet_Change(ByVal Target As Range)
   '// Runs when a value in C1 to C10 is changed
    If Not Intersect(Range("C1:C10"), Target) Is Nothing Then
        Range("B1").Value = Range("A1").Value
    End If
End Sub

To place code
Alt+F11 to open VBA Editor
In Project Pane Double Click on sheet name
Paste Code.

 

[sapter5361]

I get a compile error as soon as I change anything in my range.

I changed the variables to reflect my actual sheet.

My actual values:

Range = B3:B10
Formula Cell: B14
Values Cell: C14​

Thus I changed the code to:

Private Sub Worksheet_Change(ByVal Target As Range)
   '// Runs when a value in B3 to B10 is changed
    If Not Intersect(Range("B3:B10"), Target) Is Nothing Then
        Range("C14").Value = Range("B14").Value
    End If
End Sub

Thank you.

 

[Ralajer]

I can't reproduce the error on my machine. What is error specifically and what line of the code is highlighted when it happens? Also is there any other code in worksheet or workbook that might conflict?

 

[sapter5361]

Yes, there is other code there. It's for the purpose of reverting the data entry to UPPER case. Here it is:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, Range("B2:B9")) Is Nothing Then Exit Sub
    
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True

End Sub

Can you see any conflict?

Thank you!

 

[Ralajer]

Having both would definitively cause an conflict. Since both have the same name (Worksheet_Change) in the same scope the compiler won't arbitrarily choose one over the other therefore, it throws and error. The two can be combined easy enough.

Private Sub Worksheet_Change(ByVal Target As Range)
   Application.EnableEvents = False
   
   '// Runs when a value in B3 to B10 is changed
    If Not Intersect(Range("B3:B10"), Target) Is Nothing Then
        Range("C14").Value = Range("B14").Value
    End If
    
    If Not Intersect(Target, Range("B2:B9")) Is Nothing Then
        '// Apply only if the 1 cell is changed UCase works on strings only
        If Target.Count = 1 Then Target.Value = UCase(Target.Value)
    End If
    Application.EnableEvents = True
End Sub

 

[sa_osei]

Re: Auto Copy Cell Value from Cell with Date change

Hello Raj,

Could you help me with something similar to the below. I want to paste values cells depending on the date so should the dates change in A1, Cells B1;B7 should manually code the figures it currently has by a sign to confirm close of day.

The next date, when the date changes, the figures in B1;B7 will change so at the end of I want to create a macro button or a VBA which will automatically paste the figures in B1;B7 by values based on a formula or a cell saying 'Y' close of day. AT the moment im having to manually copy and paste values all the time at the end of the day.

THanks

I get a compile error as soon as I change anything in my range.

I changed the variables to reflect my actual sheet.

My actual values:

Range = B3:B10
Formula Cell: B14
Values Cell: C14​

Thus I changed the code to:

Private Sub Worksheet_Change(ByVal Target As Range)
   '// Runs when a value in B3 to B10 is changed
    If Not Intersect(Range("B3:B10"), Target) Is Nothing Then
        Range("C14").Value = Range("B14").Value
    End If
End Sub

Thank you.

 

[sa_osei]

Hello Raj,

Could you help me with something similar to the below. I want to paste values cells depending on the date so should the dates change in A1, Cells B1;B7 should manually code the figures it currently has by a sign to confirm close of day.

The next date, when the date changes, the figures in B1;B7 will change so at the end of I want to create a macro button or a VBA which will automatically paste the figures in B1;B7 by values based on a formula or a cell saying 'Y' close of day. AT the moment im having to manually copy and paste values all the time at the end of the day.

THanks