moving sheets using variable location

[david763]

Hi, I have a workbook in which I need to move and rename sheets to a specific location.

I have a table of current worksheet names, names for them to be re-named to, and the location of the sheet with respect to other sheets in the workbook. Here is the code:

    Dim ws As Worksheet
    Dim Loc As Integer
    
                        
    For Each c In Sheets("admin").Range("set_copyfrom")
        Loc = c.Offset(0, 1).Value
        Sheets(c).Move before:=Sheets(Loc)
        Sheets(c).Name = c.Offset(0, -1).Value
        
    Next c

In it's current form, I get a runtime error 13 - type mismatch at the "Sheets(c).Move before:=Sheets(Loc)" line. This is true whether I define 'Loc' as an integer, double, single, variant. I profess to know little about defining variables.

'Loc' is the sheet position number within the worksheet.

Before the code breaks, it seems to be picking up 'c' and 'Loc' as I would expect, just not applying 'Loc' as I'd hope.

If I leave 'Loc' without being defined I get a compile error to the effect that function call on the left must return variant or object.

Can anyone help out with this one?

Many thanks.

 

[Kenneth Hobson]

There are two issues that you may run into.
1. The sheet index number loc, does not exist. e.g. loc ="",loc=-1, loc=3 when only 2 sheets exist, loc="ken", etc.
2. The sheet name is illegal or already exists.

It is hard to see where you are going with that. I guess it could be done but moving and then renaming the sheets can be problematic if your range of index numbers and sheet names get off. Keep in mind that once you move the sheet, the index order moves too.

Sometimes, it is better to just make a new workbook and move or copy the sheets to suit. Done via a macro of course.

In your current process or in the new workbook method, the usual practice is to move/copy before the 1st sheet or after the last sheet.

If you need code to check worksheet name existence, I can show you that.

Press F8 to execute each line of code one line at a time. Hover mouse over executed lines variables or use Debug.Print loc, will show the value of loc in the Immediate Window after executed. Show the window in the View menu of VBE if needed.

 

[david763]

Thanks Kenneth, thanks for your reply.

When the code breaks with the runtime error, the values for 'c' and 'Loc' are exactly as i expected; 'c' shows the name of the sheet that i want to move and 'Loc' shows the relative position (in this case, 6). The workbook has about 15 sheets, so 'Loc' is well in range. If i hover over the variables I see 'c' as "Sheet10", for example and 'Loc' as 6.

Also, if i hard code these, the code executes without fault.

Seems to be hung up on the definition of 'Loc'.

Regards.

 

[Kenneth Hobson]

I am guessing that it is probably a duplicate sheet naming issue or the loc issue. In a workbook copy, try a loop where you just rename the sheets without moving them to test that theory. I would do a debug.print loc and other debug.prints to get a picture of what is going on. Sometimes run-times errors are not that obvious without some investigation like this.