Can you check my code please

[ipbr21054]

I have a code on a worksheet which i would like to use on another sheet.
I changed the range etc to suit the new sheet but the sheet just ended up in a right mess when i run it.

The sheet i need the sort code on has this info for you.
Sheet name CLONING
Row 2 is headers.
Row 3 is hidden.
Values then start in row 3
The columns in use are D-I

The values in column D of which i need to sort are numbers & letters,low number to high number

So i used the code below where i changed the following
A5:Q to D3:I
Sheet name DATABASE to CLONING
=Range & .Range A6 to =Range & .Range to D4

Private Sub ChipSortSort_Click()    Dim x As Long
        Application.ScreenUpdating = False
        With Sheets("DATABASE")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
            .Range("A5:Q" & x).Sort key1:=Range("A6"), order1:=xlAscending, Header:=xlGuess
    End With
    ActiveWorkbook.Save
       Application.ScreenUpdating = True
    Sheets("DATABASE").Range("A6").Select
End Sub

 

[Jeffrey Mahoney]

Since this code is placed on the SHEET level module, you can use activesheet instead of a specific sheet name.

Private Sub ChipSortSort_Click()
  Dim x As Long
  Dim Sht As Worksheet
  
  Set Sht = ActiveSheet
  Application.ScreenUpdating = False
  With Sht
    If .AutoFilterMode Then .AutoFilterMode = False
    x = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Range("A5:Q" & x).Sort key1:=Range("A6"), order1:=xlAscending, Header:=xlGuess
  End With
  ActiveWorkbook.Save
  Application.ScreenUpdating = True
  Sht.Range("A6").Select
End Sub

 

[ipbr21054]

Thanks but where do I put that ?

Better still I would rather just put the correct code on the button on the sheet in question

 

[NoSparks]

What is the value of x ?

 

[ipbr21054]

I don’t even know what the x is so I left it as is.

 

[NoSparks]

best you find that out before going any further

 

[Jeffrey Mahoney]

The code you provided is code tied to a button on the sheet DATABASE. You wanted the same code to work on the sheet CLONING. The code will actually work on any sheet as long as the button name is the same.

Copy the code I gave above to the sheet module called CLONING. Copy the Button from DATABASE to CLONING if you haven't already done so and make sure the name of the button is the same.

If you don't know about Sheet level modules then read this. The can be found in the VBA window on the left in the Tree view. Look under VBA project (named by the workbook), then Microsoft Excel Objects, then "Sheet1(Database)" (or something similar).

Jeff

 

[ipbr21054]

I did the button fine.
I also did the code fine.

When I clicked the button the sort was a mess and page all over the place.

 

[ipbr21054]

I mean what is x ?

 

[Jeffrey Mahoney]

X in your code is the last row in column A that isn't blank. The next line uses X to define the range to sort
So if X ends up being 242 then this part of the next line:
.Range("A5:Q" & x)
Would result in:
A5:Q242