Quick Move

In my continuing series of tools to save myself literally seconds every day, I present Quick Move. See also QuickTTC, QuickPivot, and QuickFind.

Even though I’m a keyboard guy, I tend to move or copy sheets within a workbook using the mouse. I generally only use Edit – Move or Copy Sheets when I want to move to another workbook. So there’s no option here to move within the same workbook. Other non-features:

  • Select different workbook without navigating to drop down
  • Always puts sheet at the end
  • If it can’t move, it copies with no message
  • Copy is a button rather than a checkbox

You can download QuickMove.xla.zip. Access the dialog box via Edit – Quick Move.

As to my moving sheets quandry, I settled on this:

Private Sub MoveSheet(ByVal bNewWorkbook As Boolean, Optional ByRef shAfter As Object)
       
        Dim sh As Object
       
        On Error Resume Next
            ActiveSheet.Move
            Select Case Err.Number
                Case 1004
                    If LCase(Right$(ActiveSheet.Parent.Name, 4)) = “.csv” Or _
                        LCase(Right$(ActiveSheet.Parent.Name, 4)) = “.txt” Or _
                        Len(ActiveSheet.Parent.Path) = 0 Then
                       
                        Set sh = ActiveSheet
                        If bNewWorkbook Then
                            ActiveSheet.Copy
                        Else
                            ActiveSheet.Copy shAfter
                        End If
                        sh.Parent.Close False
                    Else
                        If bNewWorkbook Then
                            ActiveSheet.Copy
                        Else
                            ActiveSheet.Copy shAfter
                        End If
                    End If
                Case Else
                    MsgBox Err.Description
            End Select
        On Error GoTo 0
       
End Sub

If moving creates an error, it checks the file extension. CSV, text, and unsaved files are closed once the sheet is copied. For all other files, the sheet is just copied. A little less elegant than I hoped, but it’s tailored for exactly what I want it to do.

Posted in Uncategorized

6 thoughts on “Quick Move

  1. You should set up a separate page with all your downloads. It will be easier than jumping around to different pages to find everything.

  2. My idea:

    Private Sub UserForm_Activate()
      lbxWorkbooks.Tag = “”
      For Each wb In Application.Windows
        If wb.Parent.Name  ActiveWorkbook.Name And wb.Visible Then lbxWorkbooks.Tag = lbxWorkbooks.Tag & wb.Parent.Name & “|”
      Next
      lbxWorkbooks.List = Split(lbxWorkbooks.Tag & “New Workbook”, “|”)
       
      cmdCopy.Visible = lbxWorkbooks.ListCount > 0
      cmdMove.Visible = cmdCopy.Visible

      If lbxWorkbooks.ListCount > 0 Then lbxWorkbooks.Selected(0) = True
    End Sub

    Private Sub cmdCancel_Click()
      Hide
    End Sub

    Private Sub cmdCopy_Click()
      CopyMoveSheet
    End Sub

    Private Sub cmdMove_Click()
      Application.DisplayAlerts = False
      With ActiveSheet
        cmdMove.Tag = .Parent.Name
        cmdCopy.Tag = .Name
        cmdCancel.Tag = .Parent.Path
      End With
       
      CopyMoveSheet
       
      With Workbooks(cmdMove.Tag)
        If InStr(“.txt.csv”, Right(cmdMove.Tag, 4)) > 0 Or cmdCancel.Tag = “” Then
          .Close False
        ElseIf .Sheets.Count > 1 Then
          .Sheets(cmdCopy.Tag).Delete
        End If
      End With
    End Sub

    Private Sub CopyMoveSheet()
      If lbxWorkbooks.Value = “New Workbook” Then
        ActiveSheet.Copy
      Else
        With Workbooks(lbxWorkbooks.Value)
          ActiveSheet.Copy , .Sheets(.Sheets.Count)
        End With
      End If
      Hide
    End Sub

  3. Two comments on the moving sheets code.

    You set sh to Activesheet, then continue to use Activesheet in the code.

    shAfter is an optional argument, but you do not test in the code whether it is present or not and take mitigating action, so if it is not passed, the code could fail.

  4. I want to use Activesheet. I store it in Sh so I can do the sh.Parent.Close after the activesheet has changed, but I think using Activesheet is clearer. I hadn’t thought about how that looks until you mention it. Good point on the optional argument – you don’t think I would ever forget an optional argument when I needed it do you? :)

  5. Dick,

    I realised why you captured it, just felt it looked odd. It is the sort of thing that the next guy who maintains the code might think, this sh variable is only used in one place, I can easily delete it and use ACtivesheet where it is used.

    As you want to capture the original workbook to close, I would use

                            Set wb = ActiveSheet.Parent
                            If bNewWorkbook Then
                                ActiveSheet.Copy
                            Else
                                ActiveSheet.Copy shAfter
                            End If
                            wb.Close False
                        Else
                            If bNewWorkbook Then
                                ActiveSheet.Copy
                            Else
                                ActiveSheet.Copy shAfter
                            End If
                        End If

    that way you use Activesheet which you think is clearer, you don’t have the apparent inconsistency, and your intent is actually clearer.

    All in the cause of ‘Defensive Programming’


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.