Merge 2 codes into 1

[ipbr21054]

Hi,
Can you please advise the way to merge the following codes.

Then i will have the 1 button the press to run both codes.

Many Thanks for all your help.

Sub LEADERBOARD()    '' leaderboard Macro'    Range("C1:F17").Copy Range("I1")
    Worksheets("HONDA SHEET").Range("C1:D17").Copy Worksheets("SOLD ITEMS").Range("C2:D19")
    Worksheets("HONDA SHEET").Range("E1:F17").Copy Worksheets("SOLD ITEMS").Range("C19:D35")


    ActiveWorkbook.Worksheets("SOLD ITEMS").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("SOLD ITEMS").Sort.SortFields.Add Key:=Range("D2"), _
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortTextAsNumbers
    With Worksheets("SOLD ITEMS").Sort
        .SetRange Range("C2:D35")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


    With Worksheets("SOLD ITEMS").Range("C2:D35").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        End With
    Application.Goto Sheets("SOLD ITEMS").Range("A5")
    
    End Sub

Then code below added after the above code,

Private Sub CommandButton1_Click()Dim myStr As String
Dim x As Integer
Dim myRange As Range


Set myRange = Sheets("SOLD ITEMS").Range("C2:D35")




myData = myRange.Value




For x = 1 To UBound(myData, 1)
    myStr = myStr & myData(x, 1) & vbTab & myData(x, 2) & vbCrLf
Next x




MsgBox myStr




End Sub

 

[ipbr21054]

The message box table is shown fine.

The problem is what looks like a copy of the range C1:F17 also being copied & shown.

See post #5 for the item that needs not to be shown.

 

[Joe4]

Do you have two monitors?
If so, place the VB Editor one screen and the worksheet on the other.
If not, do a split screen so you have the VB Editor on one half and the worksheet on the other.
Then, in the VB Editor, click on the title of the first macro, and press F8. This will allow you to step through your macro, one line at a time, and you can watch and see what happens on the screen at the same time.

So go ahead and do that, and find out what line of code makes that box you do not want to appear. Then you will know what section of code we will need to focus on in order to correct the issue.

 

[ipbr21054]

Ok
I did that and once the End With is shown in yellow i then see the item appear on the right hand split screen.

Also having said that if i remove this code shown below then the message table is shown on its own without the item we dont want BUT its not in order.

    With Worksheets("SOLD ITEMS").Sort        .SetRange Range("C2:D35")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply


    End With

 

[Joe4]

OK, back to your message box code, what if you place the range select line just BEFORE the MsgBox line, i.e.

Range("A1").Select
MsgBox myStr

 

[ipbr21054]

I assume you mean this is in the Macro10 code if so its the same.

Can the Macro10 code do the sort as opposed to the other code doing the sort ?
The code i mentioned in #23 is the sorting but the issue is not shown hence asking Macro10 to sort it ?

 

[Joe4]

You can try adding the Select statement right under the sort, if you like.
I think we may just to select something in between the sort, and the displaying of the MsgBox.
Where that select happens may not matter, as long as it is somewhere between the two.

 

[ipbr21054]

OK

I tried that but still the same.

 

[Joe4]

OK. I don't think there is anymore that I can do until I am able to download the file.
I should be at a location tonight or this weekend where I can download it and take a look at it.

 

[ipbr21054]

OK
Many thanks.

Have a nice weekend.

 

[Joe4]

OK. I downloaded the file, and I cannot recreate the problem you are having.
But I notice that you have frozen panes on that sheet so that you cannot scroll down. Try removing the Freeze Panes and see if that solves your issue.

Otherwise, I have no other suggestions, as I cannot recreate your situation. Your sheet is pretty complex, you may have to enlist the help of a Consultant that you can work with to help you work through it.