VBA Runtime 1004 error - How to change sheets?

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
Hello all,
I'm having a problem where i try to run a column moving macro using
Code:
    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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,092
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
   With Worksheets("MHT")
      .Columns("F:F").Cut
      .Columns("A:A").Insert Shift:=xlToRight
   End With
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
Try
Code:
   With Worksheets("MHT")
      .Columns("F:F").Cut
      .Columns("A:A").Insert Shift:=xlToRight
   End With

still getting a Run-time error
slrAwND.png

OaLKnEi.png


and here is what im using to start the macros from sheet "Program Start"
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,092
Office Version
  1. 365
Platform
  1. Windows
Should work
Is the sheet protected?
Are there any merged cells?
Do you use text to columns, either manually, or via a macro?
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,092
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad you sorted it & thanks for the feedback
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,092
Office Version
  1. 365
Platform
  1. Windows
Saving the sheet should make no difference, unless you are closing & re-opening Excel.
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,714
Messages
5,833,279
Members
430,201
Latest member
Deepakpilla36

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
Top