Suggestion for macro undo

[ipbr21054]

Evening,

I have a code which has a call to macro function at the end of it, the macro is shown below.
Basically the cells C2:D27 show an un-ordered placement but then the macro once runs places it from most popular to least popular sold items.

Now this works very good BUT i overlooked that the order MUST be put back to its un-ordered placement once ive viewed it,reason being when i select a part it then updates its stock sold quantity in the wrong place.
I thought some kind of reversal would be fine.

Now i am at a loss of how to do this.
I need to view the list but once finished then this reversal should happen,it can be time related so can you suggest something please.
Best if im in control of how it works,button etc but needs to be done before i select another part or its saved or sheet closed etc.

Sub SORTSOLDITEMS()'
' SORTSOLDITEMS Macro
'


'
    Sheets("SOLD ITEMS").Range("C2:D14").Copy Sheets("HONDA SHEET").Range("C1")
    Sheets("SOLD ITEMS").Range("C15:D27").Copy Sheets("HONDA SHEET").Range("E1")
    With Sheets("HONDA SHEET").Range("C1:F13").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
         Sheets("HONDA SHEET").Select
         Range("A13").Select
         ActiveWorkbook.Save
End Sub

 

[JoeMo]

Sorry, I don't download from the web. Maybe someone else can help you.

 

[JumboCactuar]

looks like just values so maybe at start of code:

Range("I1:L17").Value = Range("C1:F17").Value

then with a button to restore it:

Range("C1:F17").Value = Range("I1:L17").Value

 

[ipbr21054]

Thanks,
Ive now worked out this.

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")
    Call SORTSOLDITEMS
    End Sub

Sub SORTSOLDITEMS()'
' SORTSOLDITEMS Macro
'


'
    Sheets("SOLD ITEMS").Range("C2:D14").Copy Sheets("HONDA SHEET").Range("C1")
    Sheets("SOLD ITEMS").Range("C15:D27").Copy Sheets("HONDA SHEET").Range("E1")
    With Sheets("HONDA SHEET").Range("C1:F17").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
         Sheets("HONDA SHEET").Select
         Range("A13").Select
         MsgBox "Leader Board Is Now Shown" & vbNewLine & "You Must Press OK To Reset The Range Once Finished", , "Range & Leader Board Msg"
         Range("I1:L17").Copy Range("C1")
         Range("C1:F17").Select
         
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("A13").Select
End Sub

Thanks for the replies.

 

[ipbr21054]

Sorry, I don't download from the web. Maybe someone else can help you.

Hi,
Thats no problem & i understand why but maybe you can advise another way like this below.

My overall range is C1:F17 but is spilt into 2 section,so it can go across the page as opposed to a hug list going down the page.

This is the layout,

Column C will be a name & Column D will be items sold.
example

C1 CAR D1 10
C2 BOAT D2 22
This goes down the page to row 17 where it then continues at E1:E17
E1 BIKE F1 3
E2 PLANE F2 16
This goes down the page to row 17

OK so that is the range / layout of my cells to concentrate on.

The code shown below is what i have been playing with but need some further help with it.

Up on a button press i see a box appear with the cell range items & sold quantities on it.

BUT it only shows the first two columns info C1:D17 i also need to be able to see the other columns info which is E1:F17
Once that edit is in place i would like to be able to see it sorted by sold quantities & as opposed to its alphabetical A-Z sort.

Many thanks & have a nice day.

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


Set myRange = Range("C1:F17")


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

 

[JoeMo]

Try this:

Private Sub CommandButton1_Click()
Dim myStr As String, myData
Dim x As Long
Dim myRange As Range
Set myRange = Range("C1:F17")
myData = myRange.Value
For x = 1 To UBound(myData, 1)
    myStr = myStr & myData(x, 1) & vbTab & myData(x, 2) & vbTab & vbTab & _
        myData(x, 3) & vbTab & myData(x, 4) & vbCrLf
Next x
MsgBox myStr
End Sub