How to Combine two Macros

[keranali]

Hi everyone how do you join two macros into one I have macro 1


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastRw As Long, ThisRw As Long

LastRw = Range("A" & Rows.Count).End(xlUp).Row
If Intersect(ActiveCell, Range("A2:I" & LastRw)) Is Nothing Then
Range("w3:w5,w8:w11").ClearContents
Else
ThisRw = ActiveCell.Row
Range("w3:w5").Value = Application.Transpose(Cells(ThisRw, "G").Resize(, 3).Value)
Range("w8:w12").Value = Application.Transpose(Cells(ThisRw, "B").Resize(, 5).Value)
End If
End Sub




And macro 2

Sub Uppercase()
' Loop to cycle through each cell in the specified range.
For Each x In Range("A1:A5")
' Change the text in the range to uppercase letters.
x.Value = UCase(x.value)
Next
End Sub


I needed both of them to work on the same sheet?

Thanks
K

 

[Shaner73]

Hi I am getting a compile error, where do I insert the second macro below "call Uppercase"

Do you have two macros?

If so, try this...

In your first macro, after End Sub, enter down twice...type in Call Uppercase. Click save. Do nothing else.

 

[keranali]

Hi I currently have one Macro in sheet one with no modules just directly in sheet one. Macro 2 I don't know where to place it its not currently in the excel workbook am I suppose to create independent modules each containing macro 1 and macro 2, then insert the line of text call uppercase.

 

[Shaner73]

Hi I currently have one Macro in sheet one with no modules just directly in sheet one. Macro 2 I don't know where to place it its not currently in the excel workbook am I suppose to create independent modules each containing macro 1 and macro 2, then insert the line of text call uppercase.

Ok....

Insert both macro's into two separate modules....

 

[keranali]

Now the first macro don't work anymore is there any way to insert everything into sheet 1 in the vba application, I just want one macro that can do both I don't want to have to use keyboard short cuts, I need the workbook when open everything is active as the sheet is real time so if I should type text it changes automatic to caps when I press enter.

Thanks for staying so far
K

 

[Shaner73]

Now the first macro don't work anymore is there any way to insert everything into sheet 1 in the vba application, I just want one macro that can do both I don't want to have to use keyboard short cuts, I need the workbook when open everything is active as the sheet is real time so if I should type text it changes automatic to caps when I press enter.

Thanks for staying so far
K

Hmmm...not sure what's going on..

This is the only other thing I can think to do...try this code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastRw As Long, ThisRw As Long

LastRw = Range("A" & Rows.Count).End(xlUp).Row
If Intersect(ActiveCell, Range("A2:I" & LastRw)) Is Nothing Then
Range("w3:w5,w8:w11").ClearContents
Else
ThisRw = ActiveCell.Row
Range("w3:w5").Value = Application.Transpose(Cells(ThisRw, "G").Resize(, 3).Value)
Range("w8:w12").Value = Application.Transpose(Cells(ThisRw, "B").Resize(, 5).Value)
End If

For Each x In Range("A1:A5")
x.Value = UCase(x.value)
Next
End Sub

 

[keranali]

Thats it Thank you Shaner thanks James & VoG, Shaner It worked excellent Thank you for taking the time to help me sort this out.

K

 

[Shaner73]

Thats it Thank you Shaner thanks James & VoG, Shaner It worked excellent Thank you for taking the time to help me sort this out.

K

That's great....you're welcome.

 

[keranali]

If I am to change the location for uppercase can I just alter this
For Each x In Range("A2:A30") to
For Each x In Range("H2:H30")

Also the sheet has slowed down is this normal.

K

 

[Shaner73]

If I am to change the location for uppercase can I just alter this
For Each x In Range("A2:A30") to
For Each x In Range("H2:H30")

Also the sheet has slowed down is this normal.

K

Yes, you can alter that.

As for slowing down, there's not much code there to slow down, but I guess it's possible.

Try this to see if it speeds up...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastRw As Long, ThisRw As Long

Application.ScreenUpdating = False

LastRw = Range("A" & Rows.Count).End(xlUp).Row
If Intersect(ActiveCell, Range("A2:I" & LastRw)) Is Nothing Then
Range("w3:w5,w8:w11").ClearContents
Else
ThisRw = ActiveCell.Row
Range("w3:w5").Value = Application.Transpose(Cells(ThisRw, "G").Resize(, 3).Value)
Range("w8:w12").Value = Application.Transpose(Cells(ThisRw, "B").Resize(, 5).Value)
End If

For Each x In Range("A1:A5")
x.Value = UCase(x.value)
Next
Application.ScreenUpdating = TRUE
End Sub

</pre>

 

[keranali]

Yes its working like a charm thanks for your help again I am trying to learn as i go along but Macros are really hard stuff.

P.S. Do you know how to get dependent data validation boxes I already have the boxes just need link make the boxes dependent.

Thanks K