Working code needs to open results in a different way

ipbr21054

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


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

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Just an update.

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

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



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

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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