Copy only specified Worksheets 1

[tcgoth]

I have a spreadsheet with multiple worksheets (17 in total.) One worksheet is serving as the database, the other worksheets are linked and pulling information from the "database" worksheet.

I need to make a copy of 12 of these worksheets and save them as filename=worksheetname with only values displayed into the directory G:\eaststaff\spring Conference Schedule 2008. How can I specify only the selected 12 worksheets to be copied/saved? Is the below code a start in the right direction? Thanks!

For Each ws In Active.Worksheets
                fname = ws.Name & ".xls"
                ws.Activate
                ws.Cells.Copy
                ws.[A1].PasteSpecial xlValues
                Active.Worksheets.SaveAs Filename:=G: & "\" & EastStaff & "\" & Spring Conference Schedule 2008 & fname, FileFormat:= _
                xls, CreateBackup:=False
            Next ws

 

[combo]

Test sheet's name in the loop, if match then execute the rest of code inside loop. The best for testing seems to be Select Case statement. If you can use some naming rules, it can be possible to use Case "BeginName" to "EndName" condition.
BTW, have you tested your code? There is no things like 'Active', 'xlValues' refers to searching.

Dim wbSource As Workbook, wbTarget As Workbook
Dim wsSource As Worksheet
Set wbSource = ThisWorkbook
For Each wsSource In wbSource.Worksheets
    Set wbTarget = Workbooks.Add(xlWBATWorksheet)
    Set wsTarget = wbTarget.Worksheets(1)
    wsSource.UsedRange.Copy
    wsTarget.Range(wsSource.UsedRange.Address).PasteSpecial Paste:=xlPasteValues
    wbTarget.SaveAs "D:\" & wsSource.Name & ".xls"
    wbTarget.Close
Next wsSource
Application.CutCopyMode = False

combo

 

[tcgoth]

Combo,

Thank you for the post and the helpful code. It works great to get the worksheets saved as files in the specified directory. I just realized that I need to PasteSpecial Values AND Formats.

Is there any way that they can both be accomplished at the same time? I ran a macro record and it doesn't look like you can do both.

Do I have to come up with some type of code that will then cycle through all the newly created files from above and copy/paste formats to each? Ugghhh.

 

[ck1999]

why not follow up


wsTarget.Range(wsSource.UsedRange.Address).PasteSpecial Paste:=xlPasteValues

with


wsTarget.Range(wsSource.UsedRange.Address).PasteSpecial Paste:=xlPasteformats

and you only need the current loop

ck1999