Keyboard shortcut to copy from a given cell
-
- 3StarLounger
- Posts: 287
- Joined: 26 Jan 2010, 17:58
- Location: Bristol, UK
Keyboard shortcut to copy from a given cell
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
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
-
- 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
Put the following code in a standard module in the workbook:
Put the following code in the ThisWorkbook module of the workbook:
This will activate/deactivate the assignment of the macro to function key F10 when the workbook is activated/deactivated.
Code: Select all
Sub CopyFromG()
ActiveCell = Range("G" & ActiveCell.Row)
End Sub
Code: Select all
Private Sub Workbook_Activate()
Application.OnKey "{F10}", "CopyFromG"
End Sub
Private Sub Workbook_Deactivate()
Application.OnKey "{F10}"
End Sub
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 287
- Joined: 26 Jan 2010, 17:58
- Location: Bristol, UK
Re: Keyboard shortcut to copy from a given cell
That works just as I wanted it to.HansV wrote:Put the following code . . .
Thanks, again, Hans.
Ian
-
- 3StarLounger
- Posts: 287
- Joined: 26 Jan 2010, 17:58
- Location: Bristol, UK
Re: Keyboard shortcut to copy from a given cell
. . . 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 haveIanWilson wrote:That works just as I wanted it to.HansV wrote:Put the following code . . .
Thanks, again, Hans.
Ian
Code: Select all
ActiveCell = Range("Question" & ActiveCell.Row)
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?
-
- 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
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)
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 12577
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Keyboard shortcut to copy from a given cell
The text starting from "but that doesn't seem to work." looks bold to me, but I cannot see any reason why.HansV wrote:...I don't see bold, so I assume that you removed it. That means I have no idea what actually happened.
StuartR
-
- Administrator
- Posts: 7192
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
Re: Keyboard shortcut to copy from a given cell
I picked up on that too - it's now logged as an 'issue'StuartR wrote:The text starting from "but that doesn't seem to work." looks bold to me, but I cannot see any reason why.
Leif
-
- 3StarLounger
- Posts: 287
- Joined: 26 Jan 2010, 17:58
- Location: Bristol, UK
Re: Keyboard shortcut to copy from a given cell
Yes, that works just fine. Thanks again.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)
IAn
-
- 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
Sorry, that was because I am using the prosilver skin. The problem that you mentioned occurs in subsilver2 only.HansV wrote:I don't see bold, so I assume that you removed it. That means I have no idea what actually happened.
I think we've pinned down the cause, and I hope that it'll be corrected soon.
Best wishes,
Hans
Hans
-
- 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
Hi Ian,IanWilson wrote:Edit: And why did the text after my code box above come out bold?
Mike has corrected the error - it should now look OK in subsilver2 too.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 287
- Joined: 26 Jan 2010, 17:58
- Location: Bristol, UK
Re: Keyboard shortcut to copy from a given cell
It does. Thanks.HansV wrote:Hi Ian,IanWilson wrote:Edit: And why did the text after my code box above come out bold?
Mike has corrected the error - it should now look OK in subsilver2 too.
Ian
-
- 3StarLounger
- Posts: 287
- Joined: 26 Jan 2010, 17:58
- Location: Bristol, UK
Re: Keyboard shortcut to copy from a given cell
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
Ian
-
- 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
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.
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
Hans
-
- 3StarLounger
- Posts: 287
- Joined: 26 Jan 2010, 17:58
- Location: Bristol, UK
Re: Keyboard shortcut to copy from a given cell
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"?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.
Ian
-
- 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
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)
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
Hans
-
- 3StarLounger
- Posts: 287
- Joined: 26 Jan 2010, 17:58
- Location: Bristol, UK
Re: Keyboard shortcut to copy from a given cell
Thanks. I'll play around with that and see how I get on.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)
Ian
-
- PlatinumLounger
- Posts: 4023
- Joined: 24 Jan 2010, 07:23
Re: Keyboard shortcut to copy from a given cell
You're a golfer then?IanWilson wrote: Thanks. I'll play around with that and see how I get on.
Ian
-
- 3StarLounger
- Posts: 287
- Joined: 26 Jan 2010, 17:58
- Location: Bristol, UK
Re: Keyboard shortcut to copy from a given cell
GeoffW wrote:You're a golfer then?IanWilson wrote: Thanks. I'll play around with that and see how I get on.
Ian