Copy specific sheets to a new workbook

[primagic]

I have the following code which works perfectly copying all sheets in current workbook to a new workbook. How do I modify so I can choose specific sheets not to copy. Can I use a range with sheetnames?

Sub CreateUKPBFile()


'Change segment selection to UKPB
Range("D3").Value = "UKPB"

 Dim Output As Workbook
    Dim Current As String
    Dim FileName As String

    Set Output = ThisWorkbook
    Current = ThisWorkbook.FullName

    Application.DisplayAlerts = False

    'Loop through each worksheet and copy paste values to new workbook.
    Dim SH As Worksheet
    For Each SH In Output.Worksheets

        SH.UsedRange.Copy
        SH.UsedRange.PasteSpecial xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=True, Transpose:=False

    Next

    'Include selected segment in filename
    Dim strFilename As String
    strFilename = Range("D3").Value
    
      
    'Get month name for filename
    Dim strMonth As String
    strMonth = MonthName(3, True)
    
    'Get current year for filename
    Dim strYear As String
    strYear = Year(Date)
    
    
    'Create filename
    FileName = ThisWorkbook.Path & "\" & strFilename & "_Business_Submission_" & strMonth & "_" & strYear & ".xlsx"
    
    'Output file
    Output.SaveAs FileName, XlFileFormat.xlOpenXMLWorkbook
    
    Workbooks.Open Current
    
    'Close output file
    Output.Close
    
    Application.DisplayAlerts = True

End Sub

 

[SkipVought]

Hi,

You're not copying sheets. You are copying the data on each sheet and using PasteSpecial to remove all formulas, then saving THE ENTIRE WORKBOOK!

You need a whole new method, not just a bit of a tweek.

I'd suggest making a list of the sheets you want to copy. Then copy those SHEETS to a new workbook. You could use your same loop. The outline of the code might be...
[pre]
1) add new workbook

2) your loop with a sheet copy to the new workbook
2a) loop thru list
2a1) test for list=sheet name
2a11) sheet copy
2a12) copy/paste special to remove formulas

3) save your new workbook
[/pre]
The macro recorder is your friend. Post back with your recorded code if you need help customizing.

 

[Andrzejek]

So which sheets don't you want to copy?

Also, do you want to always get Mar (for March) as your month?

    'Get month name for filename
    Dim strMonth As String
    strMonth = MonthName([red]3[/red], True)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.