Copy Excel Sheet

[hai2hai]

Đây là 1 đoạn code để copy 1 sheet trong file excel sang 1 file mới.

Private Function CopySheet(ByVal strSource As String, _

    ByVal strDestination As String)
    On Error Goto CopySheet_Error
    Dim objExcel As New Excel.Application
    Dim objWorkSheet As New Excel.Worksheet
    Dim objWorkBook As New Excel.Workbook
    Dim objNewWorkBook As New Excel.Workbook
    Dim fsoObject As New FileSystemObject
    'create a new excel instance
    Set objExcel = CreateObject("Excel.Application")
    'make excel invisible
    objExcel.Visible = False
    'open the source spreedsheet
    Set objWorkBook = objExcel.Workbooks.Open(strSource)
    'set the sheet to the active (first shee
    '     t in workbook)
    Set objWorkSheet = objWorkBook.ActiveSheet
    'copy the active sheet
    objWorkSheet.Copy
    'if the destination exists, kill it

    If fsoObject.FileExists(strDestination) = True Then
        fsoObject.DeleteFile strDestination
    End If

    'save the duplicate in a new spreedsheet
    '     
    Set objNewWorkBook = objExcel.Workbooks(2)
    objNewWorkBook.SaveAs (strDestination)
    'turn off excel alerts
    objExcel.DisplayAlerts = False
    'quit excel
    objExcel.Quit
    Exit Function
    CopySheet_Error:
    MsgBox "Error Number: " & Err.Number & vbNewLine & "Description: " & Err.Description
End Function

Copyright (c) by PSC

 

[nvson]

Một cách khác là:
Sub copy_sheet()
ActiveSheet.Copy
End Sub

Và một cách thủ công là:
Nhấn Edit/Move or Copy sheet (hoặc nhấn phải chuột vào tên sheet, chọn Move or copy sheet), đánh dấu tuỳ chọn Create a copy, nhấn OK.