Moving Sheets

I’m working on a utility that requires that I move a worksheet. I thought it would be pretty easy, but now I’m rethinking it. Maybe it is easy and I’m just dense. I’ll walk you through my process and you can tell me what you think.

Step 1

Activesheet.Move

What could be simpler? Oh, there’s one little problem. When I first tested this code, the workbook containing the code was active, unsaved, and contained only one sheet. If you haven’t figured it out yet, I lost all of my code because the only worksheet was moved to another workbook and the unsaved original workbook was gone. I better save if that’s the case.

Step 2

If Not ActiveSheet.Parent.Saved And ActiveSheet.Parent.Sheets.Count = 1 Then
    ActiveSheet.Parent.Save
End If
   
ActiveSheet.Move

If it’s not saved and it’s one sheet, save it. Nice, but I don’t always want to save it. Maybe I should ask.

Step 3

Dim lResp As Long
 
If Not ActiveSheet.Parent.Saved And ActiveSheet.Parent.Sheets.Count = 1 Then
    lResp = MsgBox(“Save sheet first”, vbYesNoCancel)
    If lResp = vbYes Then
        ActiveSheet.Parent.Save
        ActiveSheet.Move
    ElseIf lResp = vbNo Then
        ActiveSheet.Move
    End If
End If

A nice clean utility is turning into a piece of crap. If it’s a previously unsaved workbook, this will save it in the current directly with the current name and in the default file format. I need to Save As if it’s previously unsaved. That means I need to present a save dialog and further muck things up.

Step 4

Dim lResp As Long
 
If Not ActiveSheet.Parent.Saved And ActiveSheet.Parent.Sheets.Count = 1 Then
    lResp = MsgBox(“Save sheet first”, vbYesNoCancel)
    If lResp = vbYes Then
        Application.CommandBars.FindControl(, 3).Execute
        ActiveSheet.Move
    ElseIf lResp = vbNo Then
        ActiveSheet.Move
    End If
End If

Executing the commandbarbutton (id 3 is the save button) will Save or SaveAs as appropriate. If I save it and then try to Move the only sheet, I get an error. Every saved workbook has to have at least one sheet. I’m doing this in a userform, so instead of all this trouble, I’m just going to disable the Move button when it’s illegal to move.

When is it illegal to move? If you have only one sheet in a workbook that’s been saved at some point. If it’s never been saved, you can move that one sheet and the original workbook goes away. So I’ll test for a Path (meaning it’s been saved) and I’ll count the sheets. Except that if it’s a CSV or a text file, I can move a single sheet and the original “workbook” simply closes. Maybe it’s more than just CSV and TXT, I don’t know.

At this point, I’m so far from my vision of a simple Move macro that it’s time to backup and rethink. I settle on:

Step 5

On Error Resume Next
    ActiveSheet.Move
    Select Case Err.Number
        Case 1004
            MsgBox “Can’t move last sheet in workbook”
        Case Else
            MsgBox Err.Description
    End Select
On Error GoTo 0

I stopped trying to anticipate the problem and just reported it. Much simpler.

Posted in Uncategorized

13 thoughts on “Moving Sheets

  1. Side Comment:
    One thing I’ve been doing lately is dimensioning a variable like your “lResult” as type “VbMsgBoxResult”. This means that the enumerations (like “vbNo”) are available under Auto Lists in the VBE. There is also a type for “VbMsgBoxStyle” which enumerates button combinations for the message box.

  2. “I stopped trying to anticipate the problem and just reported it. Much simpler.”

    Words to live by.

  3. You’re moving possibly the only worksheet out of a possibly unsaved workbook which may not yet have been saved and which could contain VBA modules? I guess the first question would be why.

    If you really have to move rather than copy worksheets, you could always check whether that were the only worksheet in its workbook, and if so create a new, blank worksheet first, then move the desired worksheet. Optionally add a dialog following the worksheet move that asks the user whether they want to close the source workbook or leave it open with a single blank worksheet.

  4. When you move a worksheet from a single sheet unsaved workbook, the workbook goes away. If I copied the sheet, I’d have a workbook that I’d still have to deal with. That’s the only reason I would choose Move instead of copy – to save a few milliseconds. The most common use I have for this is opening a CSV and moving it into a bigger workbook. I generally want the CSV to close because I have no further use for it.

    AlexJ: I did that once a few years ago and it crashed Excel. Or maybe something else crashed Excel and I blamed it on that. Anyway, I haven’t done it since. Let me know if you ever run into problems with it. It sure is handy getting the intellisense, I agree.

  5. Dick: I’ve been using this for a number of months on multiple server deployed apps. No problem so far as I know.

  6. If this is mostly for CSV files, and if you’re using Excel 2000 or later, why are you opening the CSV file rather than using Data > Import External Data > Import Data to bring them into existing workbooks?

  7. “If I save it and then try to Move the only sheet, I get an error.”

    Really? If you try to move the only visible sheet, and there is at least one hidden sheet, then you get an error. I don’t see why you can’t move the only sheet in a workbook, saved or unsaved.

  8. fzz: If you don’t put smiley emoticons after your statements, I can’t tell if you’re being facetious. Importing takes about 50 more keystrokes than opening. If I import a file with a csv extension, why does it make me go through a wizard? Of course it’s delimited, just import it.

    Jon: When I

    activesheet.move

    with a saved, single sheet workbook, I get “Move method of worksheet class failed.”

  9. If you use macros to move CSVs opened as single worksheet files into other workbooks, why not use macros to import CSVs in the first place rather than just opening them? That is, something like

    Sub foo()
      Const TEMPNAME As String = “delete_me”

      Dim ws As Worksheet, fn As String, k As Long

      fn = Application.GetOpenFilename( _
       FileFilter:=“Text Files (*.prn; *.txt; *.csv), “ & _
       “*.prn; *.txt; *.csv”, Title:=“Pick a file to open” _
      )

      If fn = “False” Then
        Exit Sub
      Else
        Set ws = ActiveWorkbook.Worksheets.Add
        k = InStrRev(fn, “”) + 1
        ws.Name = Mid$(fn, k, InStrRev(fn, “.”) – k)
      End If

      With ActiveSheet.QueryTables.Add( _
       Connection:=“TEXT;” & fn, _
       Destination:=ws.Range(“A1”) _
      )
        .Name = TEMPNAME
        .SaveData = True
        .AdjustColumnWidth = False  ‘my usual preference
       .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .RefreshPeriod = 0
        .RefreshStyle = xlInsertDeleteCells
        .Refresh BackgroundQuery:=False  ‘note: mandatory

      End With

      Application.DisplayAlerts = False
      ActiveSheet.QueryTables(TEMPNAME).Delete
      Application.DisplayAlerts = True

    End Sub

  10. Good point, fzz. If it’s a macro, it’s the same keystrokes for importing. Thanks for the code.

    Jon: We must be talking about two different things. This code errors for me:

    Sub SheetMoveError()
       
        Dim lOldSheets As Long
       
        lOldSheets = Application.SheetsInNewWorkbook
        Application.SheetsInNewWorkbook = 1
        Workbooks.Add
        Application.SheetsInNewWorkbook = lOldSheets
        ActiveWorkbook.Save
        ActiveSheet.Move ‘I get error here
       
    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *