Getting code to find the selected Active cell

[JO505]

Being a novice in VBA I have run into a problem with some code.

What I want to do is to beable to select a cell in the range K8:K26 and have the value in that cell copied to cell Z2.

Here is what I have been able to put together so far but I cant seem to get the code to find the first active cell I have selected.

Please Login or Register  to view this content.

JIm O

[pjwhitfield]

Remove the Select from ActiveCell line:

Please Login or Register  to view this content.

[6StringJazzer]

Your code does not check that the selection is in the specified range. If it matters, it should be checked.
You do not need the Select.
Why are you using Offset? That does not copy the active cell, it copies the cell 7 columns to the right of the active cell.

The following code does what your description says:

What I want to do is to beable to select a cell in the range K8:K26 and have the value in that cell copied to cell Z2.

But what do you mean the "first active cell I have selected"?

Please Login or Register  to view this content.

[JO505]

pj's suggestion worked fine.

How do I limit the selection to only range K8:K26?

JIm O

[6StringJazzer]

If pjwhitfield's suggestion worked for you then there's something about what you want your code to do that you didn't describe. That code (and your code) takes a value selected in column K and copies the value from column R in the same row into Z2. Is that what you need, rather than "select a cell in the range K8:K26 and have the value in that cell copied"?

My If statement checks to see if the active cell is in K8:K26, and does the copy only if it is in that range. Your code (and pjwhitfield's update) will do the copy no matter where the user clicks.

[JO505]

I probably didnt describe what I am looking for as well as I should have.

I have a data set, populated through a dropdown list, that shows sales made to the selected customer. Column K (K8:K26) is a list of invoice numbers for each transaction and column "R" (7 columns to the right of the invoice number) is the row number for each transaction. My final goal is to be able to select an invoice number, click a button 'shape' and open the sales log to that transaction. I would also like to limit the code to only run if a cell in the column K range is selected to prevent the wrong data to be copied into cell Z2.

Here is the code I have to open the sales log.

Please Login or Register  to view this content.

As I said I am very new to VBA and I am sure there are better ways to go about this but this is where I am now and am trying to learn.

Jim O

[JO505]

To the MODs,

There are 2 posts about this topic that I mistakenly started. The other post can be deleted.

To others,

I would still like to combine the 2 codes I have posted i this thread.
I apologize for the disconnction in this post. My only excuse is that not knowing VBA well enough, and starting out on the wrong foot.

As I said in the last post I have a data set populated from a drop down list (J8:R26) and I would like to select any cell in column K and only column K ( K contains the invoice number) before the code will run. When selected the sheet 'Sales Log' will open to the selected row.

Hopefully this is not too confusing.

Jim O