Copy certain sheets from Workbook to new Workbook with code (1 Viewer)

aldeb · Jan 13, 2009

I have an Excel workbook with 14 sheets. Twelve of the sheets are named
for the Months of the Year i.e. January, February, March, etc..

I would like to use VBA macro to copy the Twelve sheets that are named
Months to a new location and give it a name on the fly as a new workbook.

Location is:
s:\Eng\Attendance_Vacation\2009
Name of workbook:
AJTimeSheet

Any help out there with this request?

Bilbo_Baggins_Esq · Jan 13, 2009

You should be able to easily record this process in a vba "macro" and then edit the code to your specific need.

If i were to post some code for you, that is all I would do to generate it.

It seems more efficient and more educational if you just record it yourself.

What version of Excel are you using?

aldeb · Jan 14, 2009

Thanks Bilbo Baggins,

I hope others out there might have some more information than you. I have been trying
things for two days. You cannot learn VBA that quickly and I really need to get this
solved. If you can help please do, if not I hope someone else can.

Bilbo_Baggins_Esq · Jan 14, 2009

YOu need not learn VBA to record it, that is the whole idea behind recording it.

in the time it took you to write the last post, you could have recorded your own macro, and learned something at the same time...

This macro took 30 seconds to record, and required no knowledge of VBA.

Code:

Sub Copy_Sheets()
' Copy_Sheets Macro
    Sheets(Array("January", "February", "March", "April", "May", "June", "July", "August", _
        "September", "October", "November", "December")).Select
    Sheets(Array("January", "February", "March", "April", "May", "June", "July", "August", _
        "September", "October", "November", "December")).Copy
    ActiveWorkbook.SaveAs Filename:= _
        "C:\AJTimeSheet.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

sigh...

aldeb · Jan 14, 2009

Thanks Bilbo Baggins.
If you know nothing about arrays and code you can't type it in a few minutes.

I have settled on this.

I want to thank everyone for their input. The below code is
what is working for me.

Code:

Sub MoveSheets()

Worksheets(Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")).Copy

Set wbNew = ActiveWorkbook
With wbNew
    ActiveWorkbook.SaveAs Filename:="S:\Eng\Attendance_Vacation\2009" & "\AJTimesheet.xls"
    .Close
End With
End Sub

Bilbo_Baggins_Esq · Jan 14, 2009

aldeb,
I feel like I might be writing in greek or something.

I wrote NO CODE in that snippet.

zero, nada, zilch, neyt, nine.

It was all done with the macro recorder.
NONE of it was typed out by hand.

Do you know what I mean when I say you can "record a macro"?
If not, that is fine, but you might have asked, and also answered what version you're using because I was planning to rather provide step by step instructions for you to learn how to record.

If you place a fish on a person's plate, he/she never learns how to fish.

There are some really complex issues that get presented in these forums, and in some of those cases it may take some advanced knowldge of VBA.

This however is a very simple issue and one that presents an ideal opportunity for a member to learn, if that member is willing.

aldeb · Jan 14, 2009

Thanks Bilbo Baggins!

I understand Macros a littlebit. I do not understand how you did your particular
macro but I appreciate your help. Also, I am trying to learn everyday and do quite
well with VBA in Access but have never used it with other programs. I am definitely
interested in how you did your MACRO. That would be a big help.

By the way I am using Office 2003

qafself · Jan 14, 2009

On your spreadsheet

Tools > Macros > record Macro

You then carry out the steps required and the recorder stores these.

Play with it!

aldeb · Jan 14, 2009

Already played with it. Thanks for the help.

Copy certain sheets from Workbook to new Workbook with code (1 Viewer)

aldeb

Registered User.

Bilbo_Baggins_Esq

Registered User.

aldeb

Registered User.

Bilbo_Baggins_Esq

Registered User.

aldeb

Registered User.

Bilbo_Baggins_Esq

Registered User.

aldeb

Registered User.

qafself

Registered User.

aldeb

Registered User.

Similar threads

Users who are viewing this thread