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).
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,062
Office Version
  1. 365
  2. 2010
What is the exact error message & number?
1613671397912.png
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,860
Office Version
  1. 365
Platform
  1. Windows
The sheet isn't hidden is it?
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,516
Office Version
  1. 365
Platform
  1. Windows
If you tried to do it manually it would give a better idea. Right click sheet tab. Is anything greyed out?
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,516
Office Version
  1. 365
Platform
  1. Windows
Ok move it to a new workbook as that what you are attempting. If it lets you i give up :)
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,062
Office Version
  1. 365
  2. 2010
Ok move it to a new workbook as that what you are attempting. If it lets you i give up :)
HOLD ON! I'm really an idiot. The sheet tab that I'm trying to move is hidden.
In the original application, it wasn't.

I'm so sorry to be such a pain on this...but, it now works as designed by Fluff. UGH!
 
Solution

Forum statistics

Threads
1,136,203
Messages
5,674,392
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