copying multiple worksheets to a new workbook

[Shabbir]

Hi

I have a file called myfile.xls. It contains five worksheets, sheeta,
sheetb, sheetc, sheetd, and sheete. I would like to copy the last four sheets
to a new workbook, test.xls. Is there any way this is possible using VBA?

thanks in advance.

 

[JLatham]

If this is a one time deal, it's quicker to do it manually. You can do it in
much less time than you can code it up.

But for VBA code, I'll tell you
#1 - yes it can be done
#2 - best way to learn how is to use the Record New Macro feature and record
what happens when you actually do it.

Examine the code created with the macro (user [Alt]+[F11] to get to the VB
Editor) to see what would need to be changed (names of workbooks involved and
names of sheets involved) to make it a more general purpose routine.

P.S. this is one good way to learn a little basic VBA coding - record macros
and examine the code generated. The bad part about recording macros is that
it doesn't do things like IF...Then and looping. Those and some other
things, you have to learn on your own.

 

[JMB]

One way, perhaps

Sub Macro1()
Dim count As Long

count = Sheets.count
Sheets(Array(count - 3, _
count - 2, count - 1, count)).Copy
ActiveWorkbook.Close savechanges:=True, _
Filename:="I:\Excel\Test"

End Sub