Working code needs to open results in a different way

[ipbr21054]

Morning all,

I currently have this working code in use "supplied below" but i need it to open the results be shown differently.

At present i click a button & i am taken to the worksheet-range in question.
This request would be to make it open a different way so i would click the button & either just have the range appear on the current screen without being taken off this page or i was thinking of a user form maybe which might be a lot easier but then wasnt sure of how to sort the list.

Sub LEADERBOARD()    '' leaderboard Macro'    Worksheets("HONDA SHEET").Range("C1:D13").Copy Worksheets("INFO").Range("AV2:AW15")
    Worksheets("HONDA SHEET").Range("E1:F13").Copy Worksheets("INFO").Range("AV15:AW27")


    ActiveWorkbook.Worksheets("INFO").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("INFO").Sort.SortFields.Add Key:=Range("AW2"), _
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortTextAsNumbers
    With Worksheets("INFO").Sort
        .SetRange Range("AV2:AW27")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


    With Worksheets("INFO").Range("AV2:AW27").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        End With
    Application.Goto Sheets("INFO").Range("AQ2")
End Sub

 

[ipbr21054]

Just an update.

I have applied the following macro to a button which now opens a new workbook.

Sub Openworkbook_Click()

    Dim xWb As Workbook
    Dim wbName As String
    On Error Resume Next
    Set xWb = Workbooks.Open("C:\Users\Ian\Desktop\REMOTES ETC\DR\HONDA SOLD ITEMS.xlsm")
    wbName = xWb.Name
    If Err.Number <> 0 Then
        MsgBox "This workbook does not exist!", vbInformation, "HONDA SOLD ITEMS"
        Err.Clear
    Else
        MsgBox "LEADERBOARD!", vbInformation, "HONDA SOLD ITEMS"
    End If
End Sub

This is where i am at now but stuck with the following.
If you could assist me please with the copying of values from one sheet to another.

Sheet with information on is called HONDA SHEET
We need to copy C1:D13 and also E1:F13

Then paste to the new sheet which is called HONDA SOLD ITEMS
Its path is here C:\Users\Ian\Desktop\REMOTES ETC\DR\HONDA SOLD ITEMS.xlsm

We need to paste to I2:J15 AND I15:J27



If it helps i have supplied the old code below which does the job but im not sure how to update it with the new sheet path/cell ref as mentioned above.

Sub LEADERBOARD()    '' leaderboard Macro'    Worksheets("HONDA SHEET").Range("C1:D13").Copy Worksheets("INFO").Range("AV2:AW15")
    Worksheets("HONDA SHEET").Range("E1:F13").Copy Worksheets("INFO").Range("AV15:AW27")


    ActiveWorkbook.Worksheets("INFO").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("INFO").Sort.SortFields.Add Key:=Range("AW2"), _
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortTextAsNumbers
    With Worksheets("INFO").Sort
        .SetRange Range("AV2:AW27")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


    With Worksheets("INFO").Range("AV2:AW27").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        End With
    Application.Goto Sheets("INFO").Range("AQ2")
End Sub


Many thanks