It inserts it at the 2nd worksheet.
I do have other code before this bit (not that that should make a difference?)
Cells.Select
Selection.Columns.AutoFit
Columns("B:C").Select
Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
Selection.ColumnWidth = 13.86
Columns("D
").Select
Selection.Delete Shift:=xlToLeft
Columns("D
").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Range("A18").Select
Cells.Select
Selection.Replace What:="*DAY>", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveSheet.Select
ActiveSheet.Move after:=Workbooks("breakdown 2013.xlsx").Sheets(Sheets.Count)
Is this causing a problem - have I left something out, or put too much in ? Should this be a separate code ?
OK, I see the problem. The Move line is looking at the wrong workbook to get the sheet count (my error).
Try this instead:
===================
With Workbooks("breakdown 2013.xlsx")
ActiveSheet.Move after:=.Sheets(.Sheets.Count)
End With
=============================
And, in your code above, there should be no need for any of your .Select statements
The following code should work just as well, and is less "cluttered", probably easier to follow and probably more efficient:
==========================================
Option Explicit
Sub foo()
With Cells
.Columns.AutoFit
With .Columns("B:C")
.NumberFormat = "[$-F400]h:mm:ss AM/PM"
.ColumnWidth = 13.86
End With
.Columns("D
").Delete Shift:=xlToLeft
.Columns("D
").Delete Shift:=xlToLeft
.Columns("E:E").Delete Shift:=xlToLeft
.Replace What:="*DAY>", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
With Workbooks("breakdown 2013.xlsx")
ActiveSheet.Move after:=.Sheets(.Sheets.Count)
End With
End Sub
====================================
And, since you have multiple workbooks open, you might want to ensure that this code only runs on the workbook/worksheet you want. By specifying ActiveSheet (and your original Cells.Select statement, as well as my With Cells line, both implicitly refer to ActiveSheet), it'll run on whatever workbook you have selected at the time.
Hard to tell what would work properly; as a minimum, something like:
if activeworkbook.Name <> "daily_time" 'or whatever your base sheet is named
code lines
....
....
else
msgbox("Wrong workbook selected")
exit sub
end if
