VBA to Copy one cell left of active cell and paste in a different cell but just one time and then make previous active cell the active cell again

[Ohyaisee]

Hi
I am having troubles finding the solution to what I want to do. I have a locked sheet where I have unlocked specific cells so the user can tab to the next cell to fill in. What I am looking to do is to copy the Field Name such as "Payee Name" and place in another cell that is blank.

I have tried this but still new to vba and know I am missing a bunch of stuff. It does it but is in a loop as it shows W12 as Active cell continuously. So my plan is to take the field name of whatever feild the user is at and populate it in W12 and then go back to the field that the user was at.

This is where I am at:
Private Sub CopyCells(ByVal Target As Range)
ActiveCell.Offset(0, -1).Select
Selection.Copy
Range("W12").PasteSpecial xlPasteAll
End Sub

Any help would be greatly appreciated!

 

[offthelip]

It is very rare with VBa that you have to use "Select" to do anything, in your case the easiest way to leave the active cell where it started is not to move , trythis really simple code:

Sub CopyCells()
ActiveCell.Offset(0, -1).Copy Range("W12")
End Sub

 

[Micron]

Best if you just explain what you want as a result and not worry about how it would be done. I say that because it seems that the W12 thing is your way of storing what cell a user was in, but I don't see that as being necessary. You probably should be using the SelectionChange event instead.

 

[Ohyaisee]

It is very rare with VBa that you have to use "Select" to do anything, in your case the easiest way to leave the active cell where it started is not to move , trythis really simple code:

Sub CopyCells()
ActiveCell.Offset(0, -1).Copy Range("W12")
End Sub

Thanks for the expedited response. I tried that and I must have a conflict somewhere as the Excel workbook shuts down on me. I did try in in another workbook and the result is what I want. My issue is that I have Private subs that change locked cells based on user input. My current macro I am sure is an eye sore but it is working fine until I hit this little issue. So for instance, the code for one if statement is in the image. My goal is for the macro to run each time the user gets a new active cell. Would it be better if I shared the whole macro that I have as a private sub on a sheet?

 

[Ohyaisee]

Best if you just explain what you want as a result and not worry about how it would be done. I say that because it seems that the W12 thing is your way of storing what cell a user was in, but I don't see that as being necessary. You probably should be using the SelectionChange event instead.

I am trying to set up a macro that populates a specific cell (in this case W12) with the Field name of the cell that the user is on so that I can then add a lookup to show the definition for the used in the cell (in this case X12). The issue is that I want it to run each time the user tabs to a new cell.

 

[Ohyaisee]

Best if you just explain what you want as a result and not worry about how it would be done. I say that because it seems that the W12 thing is your way of storing what cell a user was in, but I don't see that as being necessary. You probably should be using the SelectionChange event instead.

My idea is that I want an excel sheet for a user to fill in and I also want a Field Description on the screen for the field that they are at. Here is a screenshot of the workbook but I also have some other macros running when target fields are entered. The white space to the right is where I want to use a lookup to put definition of field so every time the user tabs definition area gives a new definition:

 

[offthelip]

I suspect that the reason the code I posted causes your workbook to shut down is because of the way you are calling the code, if you trigger the code automatically with the worksheet change event then it will crash the workbook , what you have to do is turn events on and off when writing to a cell using vba.
I think that all you actually need to do to meet your requirements is put this code in the worksheet code which uses the selection change event. ( I added the code to turn of events although it might not be necessary it is good practice:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False ' turn off events to prevent loops and crashes
Target.Offset(0, -1).Copy Range("W12")
Application.EnableEvents = True  ' turn events back on again to allow normal operation of excel
End Sub

 

[Micron]

put this code in the worksheet code which uses the selection change event

Suggested in post 3, not addressed afterwards. I see this as determining which cell was chosen (Target.Address) and based on that, write a description to W12 or wherever it is wanted. Probably a Select Case block to provide the descriptor; something like

Select Case Target.Address
   Case $A$10
        Range("W12") = "Payee Name"
   Case ...
End Select

although I have to wonder why any of this is necessary. The 'label' used for the data entry cell should tell you what you need to know. Perhaps this has been simplified for this thread, but it sure looks like "Payee name" in W12 should not be needed.

I agree that if there are other events being triggered by this (e.g. as a result of changing cell values) then I'd try preventing that as suggested. I advocate using an error handler lest a raised error leaves events turned off.