CODEWITHSUNDEEP
exceluppercasevba
user3170862
user3170862

Reputation:

Copy Text and make it upper case

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

Upvotes: 0

Views: 2347

Answers (3)

brettdj
brettdj

Reputation: 55682

  • 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

Upvotes: 1

ron
ron

Reputation: 1378

Use the UCASE function. this works for me

   ActiveCell = UCase(ActiveCell)

Upvotes: 0

L42
L42

Reputation: 19727

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.

Upvotes: 0

Related Questions