Saving to a worksheet in another file...?

[nickclingan]

Hello, my boss wants me to come up with a macro to save an entire
worksheet into another file (IE he's tired of copying and pasting).
Let's say I've got File 1 and File 2. File 1 has a worksheet that has
been approved, so it needs to be moved to File 2. I would need to
insert a new worksheet into File 2, rename the worksheet to a character
string specified by the user, then copy the whole worksheet from File 1
into the new worksheet in File 2. I have NO idea how to write a macro
to do this... anyone out there smarter than me?

 

[Pete]

If you were doing this manually you could arrange to have both
workbooks open in the same Excel window, then select the sheet you
want to move from File 1 to File 2, and just drag the sheet tab from
the File 1 Window to the File 2 window - this will move the sheet
without having to Insert a new sheet, renaming it, highlighting,
copying and pasting the data etc. If you wanted the leave the original
sheet in File 1, then you could copy the sheet in the same way by using
CTRL-drag (you mention both move and copy, so I'm not sure exactly what
you want to do).

So, if you are happy about doing this, then you can repeat the actions,
but this time turn the macro recorder on before you do it and then turn
it off at the end. You will have to edit the recorded macro so that in
future it will work on the active sheet, rather than on the "named"
sheet you happened to use when you recorded it.

However, if you did not know about CTRL-drag, you might find that this
is a lot quicker than how you (or your boss) were doing it before, so
this might be an acceptable increase in speed without having to get
involved with macros (as you seem a bit reluctant to do so).

Hope this helps.

Pete

 

[nickclingan]

Hey Pete,

Thanks, that did help a lot, I've never even tried using the macr
recorder before! (little new to learning excel

 

[Pete]

Glad to be of help, thanks for getting back.

Pete