Make a worksheet active depending on value in cell from another worksheet

Question

I have the following macro which copies the values entered on the "Add Course" worksheet to cells on the "Bloggs, Joe" worksheet. How do I change it so it uses the value from C3 in the "Add Course" worksheet instead of the hard coded "Bloggs, Joe"?

Sheets("Add Course").Select
Range("C4").Select
Selection.Copy
Sheets("Bloggs, Joe").Select
Range("C7").Select
ActiveSheet.Paste

SJR · Answer 1 · 2018-03-19T12:41:56.077

3

Learn how to avoid Select and make your code quicker, more efficient and more concise.

With WorkSheets("Add Course")
    .Range("C4").Copy WorkSheets(.Range("C3").Value).Range("C7")
End With

edited Mar 19 '18 at 12:41

answered Mar 19 '18 at 11:51

SJR

22,986
6
18
26

1

Almost as if I'm hardwired to ignore it! Thanks. – SJR Mar 19 '18 at 12:42

score 2 · Answer 2 · edited Mar 19 '18 at 11:57

2

Check out how to avoid using Select and try something like this:

Worksheets(Worksheets("Add Course").Range("C3").Value).Range("C7").Value = Worksheets("Add Course").Range("C4").Value

edited Mar 19 '18 at 11:57

Shai Rado

33,032
6
29
51

answered Mar 19 '18 at 11:51

CallumDA

12,025
6
30
52

You were so rush typing it in you forgot to add the `Range("C7")` at the end of `Worksheets(Worksheets("Add Course").Range("C3").Value)` – Shai Rado Mar 19 '18 at 11:52

score 0 · Accepted Answer · answered Mar 19 '18 at 11:45

0

Try this:

Sheets("Add Course").Select
Range("C4").Select
Selection.Copy
Sheets(Sheets("Add Course").Range("C3").Value).Select
Range("C7").Select
ActiveSheet.Paste

answered Mar 19 '18 at 11:45

drec4s

7,946
8
33
54

4

Even though your answer is correct, you should read here why you should avoid using select https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Shai Rado Mar 19 '18 at 11:53

Make a worksheet active depending on value in cell from another worksheet

3 Answers3