ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
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.

Code:
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,

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
 
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.



Code:
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
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
Code:
MsgBox myStr
Range("A1").Select
 
Last edited:
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top