Fluff routine not working

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,062
Office Version
  1. 365
  2. 2010
Fluff, in addition to the comma mess I'm having (another post), I'm having a problem with your MOVE function.

Code:
Sub MoveSheets()
   Dim UsdRws As Long, i As Long
   Dim Cl As Range
   Dim Ary As Variant
   
   With Sheets("TextFile")
      UsdRws = .Range("U" & Rows.Count).End(xlUp).Row
      ReDim Ary(UsdRws - 5)
      For Each Cl In .Range("U5:U" & UsdRws)
         Ary(i) = Cl.Value
         i = i + 1
      Next Cl
   End With
   Sheets(Ary).Move
End Sub

This worked perfectly fine in my previous situation where the names were in the S column. In a new file, the names are now in the U column.
In my test case, there's only 1 name in U5, but I get an error on Sheets(Ary).Move that the move method of sheet class failed.
Nothing has changed from your original (below) other than S is now U in the new application.

Code:
Sub MoveSheets()
   Dim UsdRws As Long, i As Long
   Dim Cl As Range
   Dim Ary As Variant
   
   With Sheets("TextFile")
      UsdRws = .Range("S" & Rows.Count).End(xlUp).Row
      ReDim Ary(UsdRws - 5)
      For Each Cl In .Range("S5:S" & UsdRws)
         Ary(i) = Cl.Value
         i = i + 1
      Next Cl
   End With
   Sheets(Ary).Move
End Sub

Am I just going crazy? (well, don't answer that).
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,862
Office Version
  1. 365
Platform
  1. Windows
What does UsdRws return when you get the error?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You need to tell it where to move to. Before:=, After:=
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,062
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

You need to tell it where to move to. Before:=, After:=
The original code worked without that specification. The only change, as I mentioned, was column S to column U.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,516
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Just check workbook isnt protected. Review-Protect Workbook.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,062
Office Version
  1. 365
  2. 2010
The workbook in which the routine exists is not protected. The added workbook isn't protected either.
As I said, it worked just find, unchanged except for the S column reference to the U column reference, in another application.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,862
Office Version
  1. 365
Platform
  1. Windows
What is the exact error message & number?
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,516
Office Version
  1. 365
Platform
  1. Windows
Ok well try and move said sheet manually. Get an error??
 

Forum statistics

Threads
1,136,206
Messages
5,674,404
Members
419,506
Latest member
mpazr001

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