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]

Select A1 after OK button press advice

Evening all,

I would like to select the cell A1 once the OK button has been pressed but i am unsure as to where or how to insert it.

Sub Macro10()'
' Macro10 Macro
'


'
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

 

[Joe4]

Re: Select A1 after OK button press advice

I think the only "OK" button that code would produce would be from the MsgBox. So just place it right under there, i.e.

MsgBox myStr
Range("A1").Select

 

[ipbr21054]

Re: Select A1 after OK button press advice

Joe4,
Thanks for that but my issue wasnt resolved.

I have an issue where if i click in A1 after clicking the OK button the problem is solved so i thought by adding Range("A1").Select to the code would be the answer to my 24 hour head scratching but i was wrong.
Me manually clicking A1 and having the code do it for me are they both the same,if you know what i mean ?

Thanks

 

[Joe4]

Re: Select A1 after OK button press advice

It is unclear exactly what you are trying to do and why.
Can you explain in more detail?

Me manually clicking A1 and having the code do it for me are they both the same,if you know what i mean ?

Then what exactly is the point of this?

 

[ipbr21054]

Re: Select A1 after OK button press advice

Ok

Here is the download link https://drive.google.com/open?id=12wJPFrddjGSIOpSbsVx54xcVIIyuy8p4

Please click on the Leader Board button & you will see

 

[Joe4]

Re: Select A1 after OK button press advice

Sorry, but I cannot download files from my current location. My workplace forbids downloads and actually blocks most of those sites.
Please just explain exactly what you want to happen and why.
What exactly is this "OK" box you are clicking? Is it the result of the Message Box, or some sort of error or warning?

 

[ipbr21054]

Re: Select A1 after OK button press advice

OK

This thread then please post #5
https://www.mrexcel.com/forum/excel-questions/1065885-merge-2-codes-into-1-a.html

 

[Joe4]

Re: Select A1 after OK button press advice

Please do not post the same question in two different threads. That is against forum rules (which I would expect users with over 1000 posts to know by now, rule 12 here: https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html).

As such, I am merging these two threads together.

 

[ipbr21054]

Re: Select A1 after OK button press advice

Hi,
Joe4 here is the info you require,if you require anything else please advise.

Here is the download link if anybody wishes to see the issue https://drive.google.com/open?id=1ncN83aQRjW5Sb6hea0zxReW5gdnB5SZh

Joe4,i understand you cant download when at work so here is a step by step.

A 17 character is pasted into cell A13 on HONDA SHEET
A new row is then created at row 21 where the pasted content from A13 is now transfered to cell A21
I work my way along this row and at cell F21 i make a selection from a drop down list.
Once the selection is made a copy of this row is sent to another sheet called HONDA LIST & the selected item is then inserted into my range of which is HONDA SHEET C1:F17

This range stays in the same order & when selection are made from F21 the range count is updated.

This is where the problem lies.
I have a button on HONDA SHEET labeled LEADER BOARD.
When i click on it the range C1:F17 is then copied to the sheet SOLD ITEMS,where it it sorted from total items sold High to Low & then pasted into the range of C2:D35
Then the Macro10 is called which then shows me a table on top of my HONDA SHEET.
But it also drags across the blue cells from the sheet SOLD ITEMS & is then sandwiched between HONDA SHEET & this table.
The blue cells is the reason why i started this thread as i dont wish to see it at all.

A few things to consider.
If the range C1:F17 is sorted then when a selection is made from cell F21 the incorrect item is update,hence when i have a SOLD ITEMS sheet.

Many thanks

 

[Joe4]

So, is the issue just that this Message Box is not showing up on the sheet you want, or not in the range you want?
If so, then where would you like it to appear?