Macro button movig to new sheet

[Justplainj]

Hi All,

I am using the following code as part of larger code to copy a sheet and giving it today's date to back up the sheet.

Worksheets("Position Code Workbook").Range("A1").Select
Sheets("Position Code Workbook").Copy After:=Sheets(3)
ActiveSheet.Name = Format(Date, "DD-MM-YY") & " Backup"

However to launch the macro I use a shape that I assigned the macro to.
When the sheet is copied, the macro button is on the copied sheet but seems to be deleted from the main sheet.

I checked the selection pane window to make sure that it is not just hidden or bugged out and gone invisible, but it does not show on the selection pane at all.

Thanks
J

PS. the entire code below

Option Explicit
Sub OpenRunCode() 'Open files run Excel VBA macro
Dim sPath As String
Dim sFil As String
Dim owb As Workbook
Dim LastRow As Long

sPath = Sheets("Position Code Workbook").Range("K1") 'Gets folder location reports are saved to
If Right(sPath, 1) <> "\" Then sPath = sPath & "\" 'Checks if the location ends with a backslash

sFil = Dir(sPath & "*.xl*") 'Captures all XL files in a directory.

'backup and remove old data
Worksheets("Position Code Workbook").Range("A1").Select
Sheets("Position Code Workbook").Copy After:=Sheets(3)
ActiveSheet.Name = Format(Date, "DD-MM-YY") & " Backup"
Worksheets("Position Code Workbook").Select
Range("A2:H2").Select
Range(Selection, Selection.End(xlDown)).Select
    Selection.EntireRow.Delete
    Range("A1").Select


Do While sFil <> "" 'Loop through all files in Folder
Application.AskToUpdateLinks = False
Application.DisplayAlerts = False

Set owb = Workbooks.Open(sPath & sFil) 'opens the workbook. make sure it is not encrypted or protected with a password

'Windows(sFil).Activate 'Activates workbook within folder specified

Cells.Select
Selection.UnMerge

'Adds column before column A
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

'Make new heading
Range("B5").Select
Selection.Copy
Range("A5").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "Company"

'specify company name
Range("A6").Select
ActiveCell.Formula = "=RIGHT($B$2,LEN($B$2)-10)"

Range("A6").Select
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))

Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False

'delete unneccesary rows
Rows("1:4").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    
Range("E:F,H:H,J:N,P:P,R:R").Select
    Selection.Delete Shift:=xlToLeft

'Select range to copy
    Range("A2:H2").Select
    'Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

'pastes information to the main workbook
'Workbooks(oFil).Activate
ThisWorkbook.Activate
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(LastRow, 1).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Workbooks(sFil).Close SaveChanges:=False

Application.DisplayAlerts = True
Application.AskToUpdateLinks = True

sFil = Dir
Loop

'Removes Duplicates
    Range("A2:H2").Select
    'Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Range(Selection, Selection.End(xlDown)).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5 _
        , 6, 7, 8), Header:=xlYes
    Range("A1").Select

    Columns("B:B").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Delete
    
    Columns("C:C").Select
    Selection.NumberFormat = "dd/mm/yyyy"
    Range("A1").Select

ThisWorkbook.Save

End Sub

 

[Justplainj]

Hi All,

I ended up solving this matter by placing the button linked to the macro on a seperate sheet named READ FIRST.
I placed some instructions to use the workbook on there and added the button.
This will then run the code and activate the sheet to be copied and copies it. Because the button is on the READ FIRST sheet that is unaffected by the code, there is no issue with the button going missing.

Not the ideal solution, but the only one I have at the moment.

Thanks
J

 

[smozgur]

The following code looks to be deleting the shape.

Worksheets("Position Code Workbook").Select 
Range("A2:H2").Select 
Range(Selection, Selection.End(xlDown)).Select 
Selection.EntireRow.Delete

Either try to place the shape on the first row in the worksheet, or, as the best solution, right click on the shape, properties, and select "Don't move or size with cells" to retain it when the rows are deleted that the shape is placed on.