moving sheets using variable location


New Member
Apr 3, 2012
Office Version
  1. 365
  1. Windows
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.

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.
Last edited:
Upvote 0
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'.

Upvote 0
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.
Upvote 0

Forum statistics

Latest member

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