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.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,530
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



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
46,530
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
46,530
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
46,530
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,350
Messages
5,528,191
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top