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:
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.
You should set up a separate page with all your downloads. It will be easier than jumping around to different pages to find everything.
My idea:
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
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.
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? :)
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
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’
Yeah, that’s better. I need to name wb something different like wbOriginal or wbCaller and it will be even clearer. Thanks Bob.