move worksheet to end of another workbook

[sco...@ftl.co.nz]

Hi,
I'm using Excel 2010 and have two spreadsheets that I want to merge.
One of these is my timesheet workbook (called "breakdown 2013.xlsx") and the other is my daily time.
I have managed to create a macro that will mostly do what I want, but I cannot find the code to get the daily sheet to insert at the end of the "breakdown" without having to constantly change the number of sheets.

The code I'm using is :

ActiveSheet.Select
ActiveSheet.Move after:=Workbooks("breakdown 2013.xlsx").Sheets(135)

Can anyone assist with this - it's irking me no end.

[Ron Rosenfeld]

There's no reason to Select as part of the Move method.

ActiveSheet.Move after:=Workbooks("breakdown 2013.xlsx").Sheets(Sheets.Count)

[sco...@ftl.co.nz]

I tried your formula, but it didn't insert at the end of the workbook.

[Ron Rosenfeld]

On Tue, 6 Aug 2013 21:18:48 -0700 (PDT), sco...@ftl.co.nz wrote:

>I tried your formula, but it didn't insert at the end of the workbook.

It works fine here to move the active sheet to the end of the specified workbook.

Since your original worked (ActiveSheet.Move after:=Workbooks("breakdown 2013.xlsx").Sheets(135)), I cannot think of any reason why my version shouldn't work, since all it does the formula does replace the number 135 with a number representing the count of worksheets in your breakdown 2013.xlsx workbook, unless you have changed something in the two instances.

What happens when you use the code?

With both workbooks open, and the worksheet you wish to move being active, what happens if you run just the code from the immediate window?

[sco...@ftl.co.nz]

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:D").Select
Selection.Delete Shift:=xlToLeft
Columns("D: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 ?

[Ron Rosenfeld]

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:D").Delete Shift:=xlToLeft
.Columns("D: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

[sco...@ftl.co.nz]

Oh sweet !!
That works now - thank you soooo much.
I'm not terribly proficient with VBA, but can "get by" or resort to asking my husband - although he couldn't help with this one.
The code was originally created by my creating a macro and then "going thru the motions". That's prob'ly why it's so convoluted ?
Now it works brilliantly.
Again, thank you so much.

[Ron Rosenfeld]

Yes, the macro recorder usually doesn't produce the most efficient code. But it can be workable.

Glad to help out. Thanks for the feedback.