Copy Text and make it upper case

Question

I am trying to create a macro that the user will enter their initials in a specific cell and the macro will copy the initials and paste them into a list on another tab. I am having a hard time figuring out how to convert it over to upper case any help is greatly appreciated below is my code. Thanks in advance!

    'By Initials
    Worksheets("New PN").Activate
        Range("B10").Copy
        Sheets("PN_List").Select
        Range("F1").End(xlDown).Offset(1, 0).Select
        Selection.PasteSpecial xlPasteValues
        Selection.HorizontalAlignment = xlCenter

        With Selection.Font
    .Name = "Calibri"
    .Size = 11

End With

Hello and thank you for the fast response. If you wouldn't mind showing me the whole formula with the added part I would appreciate it — , Jan 15 '14 at 01:15
As per http://stackoverflow.com/questions/7982220/differentiate-trim-and-trim-in-vba/7988125#7988125 the string version of `UCase$` is faster than the variant version `UCase` — brettdj, Jan 15 '14 at 01:16

score 1 · Answer 1 · answered Jan 15 '14 at 01:15

right-click your sheet tab PN New
View Code
copy and paste the code below

This code will then automatically add any new value/s from B10 and B12 (second cell used as an example so the code can run on a range if needed) into the first blank cell in column F on sheet *PN_List*

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim lngCnt As Long

Set rng1 = Intersect(Range("B10,B12"), Target)
If rng1 Is Nothing Then Exit Sub
Set rng3 = Sheets("PN_List").Columns("F:F").Find("*", Sheets("PN_List").[f1], xlValues, , xlPrevious, xlByRows)
If rng3 Is Nothing Then Set rng3 = Sheets("PN_List").[f1]
For Each rng2 In rng1
rng3.Offset(lngCnt + 1, 0) = UCase$(rng2)
lngCnt = lngCnt + 1
Next rng2
End Sub

plus one, for covering all what the OP wants. a little bit advance logic though. :D — L42, Jan 15 '14 at 01:23

score 0 · Answer 2 · answered Jan 15 '14 at 00:31

0

Use Ucase function like this:

Dim initial as String

initial = Ucase(Range("B10").Value)
Range("F1").End(xlDown).Offset(1, 0).Value = initial

Hope this helps.

answered Jan 15 '14 at 00:31

L42

19,427
11
44
68

Hello and thank you for the fast response. If you wouldn't mind showing me the whole formula with the added part I would appreciate it – Jan 15 '14 at 01:15
I think brettdj already covered the most part for you. :) but what do you mean by `showing me the whole formula with the added part`? – L42 Jan 15 '14 at 01:27

score 0 · Accepted Answer · answered Jan 15 '14 at 00:32

0

Use the UCASE function. this works for me

   ActiveCell = UCase(ActiveCell)

answered Jan 15 '14 at 00:32

ron

1,456
3
18
27

Thank you this is exactly what I wanted! Your the best! – Jan 15 '14 at 14:42

Copy Text and make it upper case

3 Answers3