VBA Runtime 1004 error - How to change sheets?

[DataBlake]

Hello all,
I'm having a problem where i try to run a column moving macro using

    Worksheets("MHT").Activate

 Dim lastRow As Long
 lastRow = Range("A" & Rows.Count).End(xlUp).Row


    Columns("F:F").Select
    Selection.Cut
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight

the issue is that im running the macro from another sheet
if i remove the worksheet activate or select the code works perfectly
how can i start this macro from any other sheet and it go to the one i want it to and perform the column moving?

any help is greatly appreciated.

 

[Fluff]

Try

   With Worksheets("MHT")
      .Columns("F:F").Cut
      .Columns("A:A").Insert Shift:=xlToRight
   End With

 

[DataBlake]

Try

   With Worksheets("MHT")
      .Columns("F:F").Cut
      .Columns("A:A").Insert Shift:=xlToRight
   End With

still getting a Run-time error

and here is what im using to start the macros from sheet "Program Start"

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Address = "$C$2" Then

   MsgBox "Updating ATS"

   Call ATSwheelpros
End If

If Target.Address = "$D$2" Then

   MsgBox "Updating ATS"

   Call ATSvision
End If

If Target.Address = "$E$2" Then

   MsgBox "Updating ATS"

   Call ATSmht
End If

End Sub

 

[Fluff]

Should work
Is the sheet protected?
Are there any merged cells?
Do you use text to columns, either manually, or via a macro?

 

[DataBlake]

Should work
Is the sheet protected?
Are there any merged cells?
Do you use text to columns, either manually, or via a macro?

im assuming the sheet isnt protected because it works without With Worksheets/worksheet select/worksheet activate, but how do i check?
no merged cells and for this sheet there is no text to columns

i actually had the same problem with another sheet's sort code but it kinda just fixed itself?
basically what i want to do is double click a cell on sheet "program start"
it goes to a specific sheet and executes a macro
the macro works without the call to the sheet but i have to be on sheet MHT and run the macro manually.
so maybe instead of calling the macro and then selecting the sheet it should select the sheet and then call the macro?

 

[DataBlake]

im assuming the sheet isnt protected because it works without With Worksheets/worksheet select/worksheet activate, but how do i check?
no merged cells and for this sheet there is no text to columns

i actually had the same problem with another sheet's sort code but it kinda just fixed itself?
basically what i want to do is double click a cell on sheet "program start"
it goes to a specific sheet and executes a macro
the macro works without the call to the sheet but i have to be on sheet MHT and run the macro manually.
so maybe instead of calling the macro and then selecting the sheet it should select the sheet and then call the macro?

That actually solved the problem. Even though i have a similar sheet/macro that works the other way around.

 

[Fluff]

Glad you sorted it & thanks for the feedback

 

[DataBlake]

That actually solved the problem. Even though i have a similar sheet/macro that works the other way around.

Glad you sorted it & thanks for the feedback

just kidding i still have the problem. it works sometimes and doesn't others.
it seems that it will work if i save before running the macro with the data in MHT
it won't work if i save with an empty MHT sheet and copy paste before running the macro.
is there a workaround for this that you know of? its the most bizarre thing i've seen.

may i pm you the entire sub and you can see if it has any glaring issues?
i now see why people don't like using column select in VBA, but it seems like all iterations of it are also broken like this

 

[Fluff]

Saving the sheet should make no difference, unless you are closing & re-opening Excel.

 

[DataBlake]

Saving the sheet should make no difference, unless you are closing & re-opening Excel.

yeah so if i put the data in my MHT sheet, save, close excel, and reopen it. . . . it works.
if i save the file with nothing in MHT sheet, copy paste the data in MHT sheet, run the VBA, it doesn't work.