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
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
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
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
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
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.
It’s unclear why you didn’t just copy the worksheet.
Yeah copying makes a lot more sense
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.
“I stopped trying to anticipate the problem and just reported it. Much simpler.”
Words to live by.
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.
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.
Dick: I’ve been using this for a number of months on multiple server deployed apps. No problem so far as I know.
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?
“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.
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
with a saved, single sheet workbook, I get “Move method of worksheet class failed.”
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
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
Dick –
I tested it when you first mentioned it, and I’ve tested it some more. I don’t get that error. The sheet moves just fine.
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:
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