Macro button movig to new sheet

Justplainj

New Member
Joined
Apr 15, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
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.

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

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

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
Solution
The following code looks to be deleting the shape.

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

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

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