How to move sheets using Sheets().Move and Named Range

  • Hi there,

    I'm new to this site, so please be patient!

    The problem I am trying to solve is to that I want to move worksheets (using a simple macro) in my workbook, with the name of the worksheets to be moved specified by a named range (say, "sheets_to_exclude"). To be clear, the range contains the names of each sheet. The range is also dynamic, as the sheets to be moved are to be determined by a set of inputs which can change.

    I am not sure how to use the Sheets() object with a named range , and also am not sure if the dynamic range will cause issues. Any help welcome, I am sure it's an easy answer!

    Thanks in advance!

  • Mudasser

    Changed the title of the thread from “How to move sheet using Sheets().Move and Named Range” to “How to move sheets using Sheets().Move and Named Range”.
  • Basically you would use

    1. Sheets(Range("a1").Value).Move

    Loop through each ce3ll in the Range.

    1. Dim rCl As Range
    2. For Each rCl In Range("sheets_to_exclude").Cells
    3. Sheets(rCl.Value).Move
    4. Next rCl

    Where are you moving them to?

  • Hello and Welcome to the Forum :)

    Have you tried to turn on your macro recorder ... just to find out what would the outcome look like ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • If you want to move in the actual workbook you can define where to move them using before or after

    1. Sheets("Sheet1").Copy Before:=Sheets(1)
    2. Sheets("Sheet1").Copy after:=Sheets(Sheets.Count)

    If you don't specify a location then the sheet will be exported to a new workbook.

    If you want to move to a specific workbook

    1. ActiveSheet.Move After:=Workbooks("YourWorkbookName.xlsx"). Sheets(sheets.Count)
  • Thanks Roy. That indeed does the job! I am just moving the sheets after a tab called "Exclude>>", so have just used

    1. Sheets(Rcl.Value).Move After:=Sheets("Exclude>>")

    One more question (not sure if I need to post this as a new thread?), but what do I need to add to your code to first check that the range is NOT empty/invalid? As there is a possibility (determined by user inputs) that the dynamic range ("sheets_to_exclude") does not contain any values (i.e. the dynamic range name returns #Ref, and so the VBA code you provided does not work.. so I first need to check that the range is valid and then loop though if it is).

    Thank you so much.

    Carim, good advice, but I wasnt sure how to use record macro to record what I need in this instance, as it involved range names,