Change to UPPERCASE - My VBA Macro Imports lowercase data Sheet 1 to lowercase Sheet 2?

[John.McLaughlin]

Hello!

I use a basic order form clients fill out and send me. Then I have a VBA macro that reads every cell of the submitted form, and pastes the value into the corresponding "In House" form.

Trouble lately, a few people are sending everything in lower case. Can I modify my copy or paste command to convert this to uppercase?

Thanks in advance!

'copy the Cell from source book
    wbThis.Sheets("ORDER").Range("J4").Copy
    '
    'paste the data on the target book
    wbTarget.Sheets("ORDER").Range("F8").PasteSpecial xlPasteValues


    ' This is the end of code for individual cell copy ORDEREDBY

 

[jasonb75]

As it is only one cell, instead of copy and paste, just copy the value and convert it on the fly.

wbTarget.Sheets("ORDER").Range("F8").Value = UCase(wbThis.Sheets("ORDER").Range("J4"))

 

[John.McLaughlin]

That did the trick!

And you also just showed me how to optimize my copy/paste!

Thank you very much!

 

[John.McLaughlin]

My code is much faster now, thanks again!

I am trying to force a number format of "000.000", but it seems the target format overrides the "on the fly" source formula you showed me.

wbTarget.Sheets("SIZE").Range("D5").Value = Format(wbThis.Sheets("SIZE").Range("E3"), "000.000")

Can I change the target format?

 

[LlebKcir]

https://www.excelhowto.com/macros/formatting-a-range-of-cells-in-excel-vba/

good set of examples on formatting.

 

[John.McLaughlin]

https://www.excelhowto.com/macros/formatting-a-range-of-cells-in-excel-vba/

good set of examples on formatting.

Thanks for the tip! Great reference!

wbTarget.Sheets("SIZE").Range("D5").Value = wbThis.Sheets("SIZE").Range("E3").NumberFormat = "0.000"

Why does it paste a value of FALSE on the target.

 

[Fluff]

You need to do it like

With wbTarget.Sheets("SIZE").Range("D5")
   .Value = wbThis.Sheets("SIZE").Range("E3")
   .NumberFormat = "0.000"
End With

 

[jasonb75]

You need to do the value and format separately otherwise the second part becomes a logical test, which is why you're seeing FALSE.

wbTarget.Sheets("SIZE").Range("D5").Value = wbThis.Sheets("SIZE").Range("E3")
wbTarget.Sheets("SIZE").Range("D5").NumberFormat = "0.000"

 

[LlebKcir]

jasonb75, thank you for that bit of info. I was searching for the answer and my google-foo failed me.

 

[John.McLaughlin]

You need to do it like

With wbTarget.Sheets("SIZE").Range("D5")
   .Value = wbThis.Sheets("SIZE").Range("E3")
   .NumberFormat = "0.000"
End With

Fluff, you are the greatest MOD ever!

You need to do the value and format separately otherwise the second part becomes a logical test, which is why you're seeing FALSE.

wbTarget.Sheets("SIZE").Range("D5").Value = wbThis.Sheets("SIZE").Range("E3")
wbTarget.Sheets("SIZE").Range("D5").NumberFormat = "0.000"

Thank you Jason, it did not occur to me split the command.

jasonb75, thank you for that bit of info. I was searching for the answer and my google-foo failed me.

I'm right there with you, if I only had a Keyword Thesaurus too


Thanks to all! You are the best!