Keyboard shortcut to copy from a given cell

User avatar
IanWilson
3StarLounger
Posts: 287
Joined: 26 Jan 2010, 17:58
Location: Bristol, UK

Keyboard shortcut to copy from a given cell

Post by IanWilson »

I have a spreadsheet listing hundreds of multiple choice questions. Each question has a unique identifier in column G which is of the form U1 OV Ch 2 F6 Q3.1 (which is put there by a formula that concatenates entries from several other columns. (It means Unit 1 Overview Chapter 2 Folder 6 Question 3.1, not that it matters).

I may want to choose that question for one of three test papers, which will be listed in columns I, K or M.

What I'd like to happen is to be able to position the cursor in an empty cell in column I, K or M, press some key, such as F10 or some suitable key that is not in use for another purpose, and have the contents of the cell in that row and column G copied into the cell. (So if I was in cell K33, it would copy G33 into K33, and if I was in M48, it would copy G48..)

Can that be done? The whole point is to avoid typing again U1 OV Ch 2 F6 Q3.1. I could of course just copy and paste from the cell concerned but I wondered if there was a way of selecting a question onto my list with just a single key press. (I can obviously deselect it with the Del key.)

Ian

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Keyboard shortcut to copy from a given cell

Post by HansV »

Put the following code in a standard module in the workbook:

Code: Select all

Sub CopyFromG()
  ActiveCell = Range("G" & ActiveCell.Row)
End Sub
Put the following code in the ThisWorkbook module of the workbook:

Code: Select all

Private Sub Workbook_Activate()
  Application.OnKey "{F10}", "CopyFromG"
End Sub

Private Sub Workbook_Deactivate()
  Application.OnKey "{F10}"
End Sub
This will activate/deactivate the assignment of the macro to function key F10 when the workbook is activated/deactivated.
Best wishes,
Hans

User avatar
IanWilson
3StarLounger
Posts: 287
Joined: 26 Jan 2010, 17:58
Location: Bristol, UK

Re: Keyboard shortcut to copy from a given cell

Post by IanWilson »

HansV wrote:Put the following code . . .
That works just as I wanted it to.

Thanks, again, Hans. :cheers:

Ian

User avatar
IanWilson
3StarLounger
Posts: 287
Joined: 26 Jan 2010, 17:58
Location: Bristol, UK

Re: Keyboard shortcut to copy from a given cell

Post by IanWilson »

IanWilson wrote:
HansV wrote:Put the following code . . .
That works just as I wanted it to.

Thanks, again, Hans. :cheers:

Ian
. . . but now I have another question. If I were to insert or delete a column to the left of column G, then the column to copy from would become something else, say F or H. I wondered whether I could name the existing column G as a range say "Question" and put "Question" rather than "G" so that I would have

Code: Select all

ActiveCell = Range("Question" & ActiveCell.Row)
but that doesn't seem to work. I could of course edit the macro to change G to F or H as required but I wondered if there was a way of avoiding that.

PS - I have another question, about macro security, but I'll ask that later in another thread.

Ian

Edit: And why did the text after my code box above come out bold?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Keyboard shortcut to copy from a given cell

Post by HansV »

Try the following:
  • Name the column you want to copy from "Question".
  • Change the relevant line of code to

    ActiveCell = Range("Question").Cells(ActiveCell.Row)
I don't see bold, so I assume that you removed it. That means I have no idea what actually happened.
Best wishes,
Hans

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Keyboard shortcut to copy from a given cell

Post by StuartR »

HansV wrote:...I don't see bold, so I assume that you removed it. That means I have no idea what actually happened.
The text starting from "but that doesn't seem to work." looks bold to me, but I cannot see any reason why.
StuartR


User avatar
Leif
Administrator
Posts: 7192
Joined: 15 Jan 2010, 22:52
Location: Middle of England

Re: Keyboard shortcut to copy from a given cell

Post by Leif »

StuartR wrote:The text starting from "but that doesn't seem to work." looks bold to me, but I cannot see any reason why.
I picked up on that too - it's now logged as an 'issue' :smile:
Leif

User avatar
IanWilson
3StarLounger
Posts: 287
Joined: 26 Jan 2010, 17:58
Location: Bristol, UK

Re: Keyboard shortcut to copy from a given cell

Post by IanWilson »

HansV wrote:Try the following:
  • Name the column you want to copy from "Question".
  • Change the relevant line of code to

    ActiveCell = Range("Question").Cells(ActiveCell.Row)
Yes, that works just fine. Thanks again.

IAn

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Keyboard shortcut to copy from a given cell

Post by HansV »

HansV wrote:I don't see bold, so I assume that you removed it. That means I have no idea what actually happened.
Sorry, that was because I am using the prosilver skin. The problem that you mentioned occurs in subsilver2 only.

I think we've pinned down the cause, and I hope that it'll be corrected soon.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Keyboard shortcut to copy from a given cell

Post by HansV »

IanWilson wrote:Edit: And why did the text after my code box above come out bold?
Hi Ian,

Mike has corrected the error - it should now look OK in subsilver2 too.
Best wishes,
Hans

User avatar
IanWilson
3StarLounger
Posts: 287
Joined: 26 Jan 2010, 17:58
Location: Bristol, UK

Re: Keyboard shortcut to copy from a given cell

Post by IanWilson »

HansV wrote:
IanWilson wrote:Edit: And why did the text after my code box above come out bold?
Hi Ian,

Mike has corrected the error - it should now look OK in subsilver2 too.
It does. Thanks.

Ian

User avatar
IanWilson
3StarLounger
Posts: 287
Joined: 26 Jan 2010, 17:58
Location: Bristol, UK

Re: Keyboard shortcut to copy from a given cell

Post by IanWilson »

And now I've got a further question. If I have several sheets in my workbook, can I use the same macro to use F10 to copy from the column of questions on a particular worksheet? It seems that if say column G on the first sheet is named as a range called "Question" then pressing F10 in a cell on any other sheet in the workbook copies from the first sheet. It would be good to be able to copy from the Questions column on that particular sheet. Can I do that, or would it need a separate macro and range name for each sheet?

Ian

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Keyboard shortcut to copy from a given cell

Post by HansV »

If you want to do this on multiple sheets, a named range won't help. You introduced the named range because you wanted to be able to insert or delete columns to the left of the relevant column. This makes no sense if the named range could refer to multiple sheets - what if you inserted a column into one sheet and deleted a column from another?

If you want to copy from column G on every sheet, you can use the macro from my first reply in this thread. But it would not do what you want any more if you insert or delete columns.

If you want to have a flexible range, you'd have to define a separate name and create a separate macro for each sheet.
Best wishes,
Hans

User avatar
IanWilson
3StarLounger
Posts: 287
Joined: 26 Jan 2010, 17:58
Location: Bristol, UK

Re: Keyboard shortcut to copy from a given cell

Post by IanWilson »

HansV wrote:If you want to do this on multiple sheets, a named range won't help. You introduced the named range because you wanted to be able to insert or delete columns to the left of the relevant column. This makes no sense if the named range could refer to multiple sheets - what if you inserted a column into one sheet and deleted a column from another?

If you want to copy from column G on every sheet, you can use the macro from my first reply in this thread. But it would not do what you want any more if you insert or delete columns.

If you want to have a flexible range, you'd have to define a separate name and create a separate macro for each sheet.
So is there a way to get VBA to obey an instruction along the lines of "Find the column on this sheet headed "Question" and copy the contents of the cell in that column and the current row"?

Ian

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Keyboard shortcut to copy from a given cell

Post by HansV »

You can define a sheet-specific name by preceding its name with the sheet name and an exclamation mark !, for example Sheet1!Question
You'd have to do this for each sheet separately.
You can then use a single macro with

ActiveCell = ActiveSheet.Range("Question").Cells(ActiveCell.Row)
Best wishes,
Hans

User avatar
IanWilson
3StarLounger
Posts: 287
Joined: 26 Jan 2010, 17:58
Location: Bristol, UK

Re: Keyboard shortcut to copy from a given cell

Post by IanWilson »

HansV wrote:You can define a sheet-specific name by preceding its name with the sheet name and an exclamation mark !, for example Sheet1!Question
You'd have to do this for each sheet separately.
You can then use a single macro with

ActiveCell = ActiveSheet.Range("Question").Cells(ActiveCell.Row)
Thanks. I'll play around with that and see how I get on.

Ian

GeoffW
PlatinumLounger
Posts: 4023
Joined: 24 Jan 2010, 07:23

Re: Keyboard shortcut to copy from a given cell

Post by GeoffW »

IanWilson wrote: Thanks. I'll play around with that and see how I get on.

Ian
You're a golfer then?

User avatar
IanWilson
3StarLounger
Posts: 287
Joined: 26 Jan 2010, 17:58
Location: Bristol, UK

Re: Keyboard shortcut to copy from a given cell

Post by IanWilson »

GeoffW wrote:
IanWilson wrote: Thanks. I'll play around with that and see how I get on.

Ian
You're a golfer then?
:laugh: