How to copy specific sheets into a new workbook using VBA?

You can copy specific sheets from one workbook to a new workbook using VBA by following these steps. Below is a sample code that demonstrates how to achieve this:

Sub CopySpecificSheets()
    Dim sourceWorkbook As Workbook
    Dim newWorkbook As Workbook
    Dim sheet As Worksheet
    Dim sheetsToCopy As Variant
    Dim i As Integer
    
    ' Specify the sheets you want to copy.
    ' Use the sheet names or sheet numbers as needed.
    sheetsToCopy = Array("Sheet1", "Sheet3")  ' Example sheet names to copy

    ' Set the source workbook to the active workbook.
    ' If your source workbook is different, you need to open or set it appropriately.
    Set sourceWorkbook = ThisWorkbook  ' Or you can set it to specific workbook like Workbooks("YourWorkbookName.xlsm")
    
    ' Add a new workbook where sheets will be copied.
    Set newWorkbook = Workbooks.Add
    
    ' Loop through each sheet name in the array.
    For i = LBound(sheetsToCopy) To UBound(sheetsToCopy)
        ' Set the source worksheet.
        Set sheet = sourceWorkbook.Sheets(sheetsToCopy(i))
        
        ' Copy the sheet to the new workbook.
        sheet.Copy After:=newWorkbook.Sheets(newWorkbook.Sheets.Count)
    Next i
    
    ' Optionally, delete the default sheets in the new workbook.
    ' Note: Uncomment the following lines if you want to remove default sheets.
    ' On Error Resume Next
    ' Application.DisplayAlerts = False
    ' For Each sheet In newWorkbook.Sheets
    '     If Not IsInArray(sheet.Name, sheetsToCopy) Then
    '         sheet.Delete
    '     End If
    ' Next sheet
    ' Application.DisplayAlerts = True
    ' On Error GoTo 0

    ' Display the new workbook.
    newWorkbook.Activate
    
End Sub

' Helper function to check if a value exists in an array (optional).
Function IsInArray(valueToCheck As Variant, arr As Variant) As Boolean
    Dim element As Variant
    IsInArray = False
    For Each element In arr
        If element = valueToCheck Then
            IsInArray = True
            Exit Function
        End If
    Next element
End Function

Explanation:

Specify Sheets: In the `sheetsToCopy` array, specify the names of the sheets you want to copy.
Source Workbook: The macro assumes that the source workbook is the active workbook (`ThisWorkbook`). Modify this if necessary to specify a different workbook.
New Workbook: The macro creates a new workbook and copies the specified sheets into it.
Optional Sheet Deletion: You can uncomment the section towards the end if you want to remove any default sheets from the new workbook that were not part of the sheets you copied.
Helper Function: The helper function `IsInArray` is used if you decide to implement the default sheet deletion logic.

Remember to save the new workbook as needed since the code creates it in memory without saving it automatically. Adjust the logic if your workbook setup or requirements are different.

facebooklinkedin