Fixed Macro Button

[Oakey]

I have created a macro and inserted a macro button to process the macro.

The issue im having is that my macro button disappears as the cells i put it in get deleted as part of the macro.
is there any way of fixing a macro to a page so it does not get deleted but the columns its over does?

Below is my macro

Sub Step1datasort()
'
' Step1datasort Macro
'

'
    Columns("B:L").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:G").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:G").EntireColumn.AutoFit
    Columns("E:J").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:N").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:BS").Select
    Selection.Delete Shift:=xlToLeft
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Order"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Units"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Time"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Date"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Category"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "User"
    Columns("A:F").Select
    ActiveWorkbook.Names.Add Name:="Info", RefersToR1C1:="='Data '!C1:C6"
End Sub

 

[steve the fish]

If forms button then right click button, format control, properties and finally check dont move or size with cells.

 

[davesexcel]

You may consider moving the button out of the way first before delet6ing columns. Change button name in code.
Assuming Forms button.

Sub MyCode()

    Dim c As Range, s As Range
    Set s = Range("GG1:GG1")
        With ActiveSheet.Shapes("Button 1")
        .Left = s.Left
        .Top = s.Top
        .Width = s.Width
        .Height = s.Height
    End With
'--------------------------------------------------------------------------------------
   
Columns("B:L").Delete Shift:=xlToLeft
Columns("C:C").Delete Shift:=xlToLeft
Columns("D:G").Delete Shift:=xlToLeft
Columns("D:G").EntireColumn.AutoFit
Columns("E:J").Delete Shift:=xlToLeft
Columns("F:N").Delete Shift:=xlToLeft
Columns("G:BS").Delete Shift:=xlToLeft
Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1") = "Order"
Range("B1") = "Units"
Range("C1") = "Time"
Range("D1") = "Date"
Range("E1") = "Category"
Range("F1") = "User"
Sheets("Data").Range("C1:C6").Name = "Info"
   
'===============================================
    With ActiveSheet.Shapes("Button 1")
        Set c = Range("A3:B4")

        .Left = c.Left
        .Top = c.Top
        .Width = c.Width
        .Height = c.Height
    End With

End Sub

Of course you could go to format control and tick off don't move or size with cells as suggested in previous post.

 

[jasonb75]

Compressing Dave's clean up of your code a little more.

Sub Step1datasort()
Range("B:L,N:N,P:S,U:Z,AB:AJ,AL:CX").EntireColumn.Delete
Columns("D:D").EntireColumn.AutoFit
Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1:F1").Value = Split("Order,Units,Time,Date,Category,User", ",")
ActiveWorkbook.Names.Add Name:="Info", RefersToR1C1:="='Data '!C1:C6"
End Sub

I suspect some of that could still be superfluous.

 

[Oakey]

thank you guys.