Cách sao chép một trang tính trong Excel với VBA

Hướng dẫn cung cấp một tập hợp các macro để sao chép các trang tính trong Excel: sao chép và đổi tên dựa trên giá trị ô, sao chép nhiều trang tính, sao chép một bảng tính hoạt động sang một tệp khác mà không cần mở nó, v.v.

Sao chép thủ công các trang tính trong Excel khá nhanh chóng và đơn giản … nếu được thực hiện chỉ một hoặc hai lần. Sao chép nhiều tờ nhiều lần là nhàm chán và tốn thời gian. Trên trang này, bạn sẽ tìm thấy một số macro hữu ích để tự động hóa tác vụ này.

VBA Excel để sao chép trang tính vào sổ làm việc mới

Macro một dòng đơn giản nhất này thực hiện chính xác những gì tên của nó gợi ý – sao chép trang tính hoạt động sang một bảng tính mới.

Public Sub CopySheetToNewWorkbook()
        activeSheet.Copy
End Sub

Sao chép nhiều trang tính trong Excel bằng VBA

Nếu bạn muốn sao chép một số trang tính từ sổ làm việc đang hoạt động sang một trang tính mới, hãy chọn tất cả các trang tính quan tâm và chạy macro này:

Public Sub CopySelectedSheets()
        ActiveWindow.SelectedSheets.Copy
End Sub

VBA Excel để sao chép trang tính sang sổ làm việc khác

Tùy thuộc vào nơi bạn muốn chèn bảng sao chép, sử dụng một trong các macro sau.

Sao chép trang tính vào đầu một sổ làm việc khác

Macro này sao chép trang tính hoạt động trước tất cả các bảng tính khác trong tệp đích, Cuốn sách 1 trong ví dụ này Để sao chép sang tệp khác, thay thế “Book1.xlsx” bằng tên đầy đủ của sổ làm việc mục tiêu của bạn.

Public Sub CopySheetToBeginningAnotherWorkbook()
        activeSheet.Copy Before:=Workbooks("Book1.xlsx").Sheets(1)
End Sub

Sao chép trang tính vào cuối của một bảng tính khác

Đoạn mã này sao chép bảng tính hoạt động và đặt bản sao vào cuối Cuốn sách 1. Một lần nữa, hãy nhớ thay thế “Book1.xlsx” bằng tên của sổ làm việc đích của bạn.

Public Sub CopySheetToEndAnotherWorkbook()
        activeSheet.Copy After:=Workbooks("Book1.xlsx").Sheets(Workbooks("Book1.xlsx").Worksheets.Count)
End Sub

Ghi chú. Để các macro hoạt động, sổ làm việc đích phải được lưu trên ổ cứng hoặc mạng của bạn.

Sao chép trang tính vào sổ làm việc đã chọn

Để có thể sao chép trang tính hiện tại vào bất kỳ sổ làm việc đang mở nào, bạn có thể tạo một UserForm (có tên UserForm1) với một ListBox điều khiển (tên là ListBox1) và hai nút:

Tạo một UserForm để sao chép trang tính vào bất kỳ sổ làm việc đang mở

Tiếp theo, bấm đúp vào biểu mẫu và dán mã dưới đây vào cửa sổ Mã:

Public SelectedWorkbook As String

Private Sub UserForm_Initialize()
        SelectedWorkbook = ""
        ListBox1.Clear
        For Each wbk In Application.Workbooks
                ListBox1.AddItem (wbk.Name)
        Next
End Sub

Private Sub CommandButton1_Click()
        If ListBox1.ListIndex > -1 Then
                SelectedWorkbook = ListBox1.List(ListBox1.ListIndex)
        End If
        Me.Hide
End Sub

Private Sub CommandButton2_Click()
        SelectedWorkbook = ""
        Me.Hide
End Sub

Với UserForm đã có, bạn có thể sử dụng một trong các macro sau để sao chép trang tính hoạt động vào sổ làm việc bạn chọn.

Sao chép tờ vào bắt đầu của bảng tính đã chọn:

Public Sub CopySheetToBeginningAnotherWorkbook()
        Load UserForm1
        UserForm1.Show

        If (UserForm1.SelectedWorkbook <> "") Then
                activeSheet.Copy Before:=Workbooks(UserForm1.SelectedWorkbook).Sheets(1)
        End If

        Unload UserForm1
End Sub

Sao chép tờ vào cuối sách bài tập đã chọn:

Public Sub CopySheetToEndAnotherWorkbook()
        Load UserForm1
        UserForm1.Show

        If (UserForm1.SelectedWorkbook  "") Then
        activeSheet.Copy After:=Workbooks( _
        UserForm1.SelectedWorkbook).Sheets( _
        Workbooks(UserForm1.SelectedWorkbook). _
        Worksheets.Count)
        End If

        Unload UserForm1
End Sub

Khi chạy trong Excel, macro sẽ hiển thị cho bạn một danh sách tất cả các sổ làm việc hiện đang mở. Bạn chọn cái cần thiết và nhấn OK:
Một macro để sao chép trang tính hoạt động vào sổ làm việc đã chọn

Excel macro để sao chép trang tính và đổi tên

Khi bạn sao chép một trang tính trong Excel, bản sao được đặt tên theo định dạng mặc định như Tờ1 (2). Các macro sau đây có thể giúp bạn tránh những rắc rối khi thay đổi tên mặc định theo cách thủ công.

Mã này sao chép bảng tính hoạt động, đặt tên bản sao là “Bảng kiểm tra” (bạn có thể tự do thay thế nó bằng bất kỳ tên nào khác mà bạn thích) và đặt bảng sao chép vào cuối sổ làm việc hiện tại.

Public Sub CopySheetAndRenamePredefined()
        activeSheet.Copy After:=Worksheets(Sheets.Count)
        On Error Resume Next
        activeSheet.Name = "Test Sheet"
End Sub

Cho phép người dùng chỉ định tên cho bảng sao chép, sử dụng mã này:

Public Sub CopySheetAndRename()
        Dim newName As String

        On Error Resume Next
        newName = InputBox("Enter the name for the copied worksheet")

        If newName <> "" Then
                activeSheet.Copy After:=Worksheets(Sheets.Count)
                On Error Resume Next
                activeSheet.Name = newName
        End If
End Sub

Khi chạy, macro hiển thị hộp nhập liệu sau, trong đó bạn nhập tên mong muốn và nhấn OK:
Excel macro để sao chép trang tính và đổi tên

Macro Excel để sao chép trang tính và đổi tên dựa trên giá trị ô

Trong một số trường hợp, có thể thuận tiện hơn khi đặt tên một bản sao với giá trị ô cụ thể, ví dụ: tiêu đề cột. Đối với điều này, bạn chỉ cần lấy mã ở trên và cung cấp giá trị của ô hiện được chọn vào hộp đầu vào tự động. Như với ví dụ trước, bản sao sẽ được đặt ở cuối sổ làm việc đang hoạt động.

Macro Excel để sao chép trang tính và đổi tên dựa trên giá trị ô

Phần khó nhất là người dùng của bạn luôn chọn đúng ô trước khi chạy macro 🙂

Public Sub CopySheetAndRenameByCell()
        Dim newName As String
        On Error Resume Next
        newName = InputBox("Enter the name for the copied worksheet", "Copy worksheet", ActiveCell.Value)

        If newName <> "" Then
                activeSheet.Copy After:=Worksheets(Sheets.Count)
                On Error Resume Next
                activeSheet.Name = newName
        End If
End Sub

Ngoài ra, bạn có thể mã hóa địa chỉ của ô theo đó bản sao sẽ được đặt tên, ô A1 trong đoạn mã dưới đây. Để đặt tên cho bảng tính được sao chép dựa trên một ô khác, thay thế A1 bằng một tham chiếu ô thích hợp.

Public Sub CopySheetAndRenameByCell2()
        Dim wks As Worksheet
        Set wks = activeSheet
        activeSheet.Copy After:=Worksheets(Sheets.Count)
        If wks.Range("A1").Value <> "" Then
                On Error Resume Next
                activeSheet.Name = wks.Range("A1").Value
        End If

        wks.Activate
End Sub

Macro để sao chép bảng tính vào một sổ làm việc đã đóng

Macro này sao chép trang tính hoạt động vào cuối của một sổ làm việc đã đóng. Tên của một sổ làm việc khác không được chỉ định trong mã – macro sẽ mở cửa sổ Windows Explorer tiêu chuẩn và cho phép bạn chọn bất kỳ tệp đích nào:

Macro để sao chép bảng tính vào một sổ làm việc đã đóng

Sau khi bạn chọn tệp và nhấp vào Mở, macro sẽ sao chép trang tính hoạt động và tự động đóng sổ làm việc đích.

Public Sub CopySheetToClosedWorkbook()
        Dim fileName
        Dim closedBook As Workbook
        Dim currentSheet As Worksheet

        fileName = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx")

        If fileName <> False Then
                Application.ScreenUpdating = False

                Set currentSheet = Application.activeSheet
                Set closedBook = Workbooks.Open(fileName)
                currentSheet.Copy After:=closedBook.Sheets(closedBook.Worksheets.Count)
                closedBook.Close (True)

                Application.ScreenUpdating = True
        End If
End Sub

VBA Excel để sao chép trang tính từ một sổ làm việc khác mà không cần mở

Macro này cho phép bạn sao chép một bảng tính từ một tệp Excel khác mà không cần mở nó. Bảng sao chép sẽ được chèn vào cuối sổ làm việc hiện tại.

Chỉ cần nhớ để thực hiện một vài thay thế trong mã:

C: Users XXX Documents Target_Book.xlsx nên được thay đổi thành đường dẫn và tên thực tế của sổ làm việc mà bạn muốn sao chép một trang tính.
Trang tính 1 nên được thay thế bằng tên của trang tính bạn muốn sao chép.

Public Sub CopySheetFromClosedWorkbook()
        Dim sourceBook As Workbook
        Application.ScreenUpdating = False
        Set sourceBook = Workbooks.Open("C:UsersXXXDocumentsTarget_Book.xlsx")
        sourceBook.Sheets("Sheet1").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        sourceBook.Close
        Application.ScreenUpdating = True
End Sub

VBA Excel để nhân đôi trang tính nhiều lần

Đôi khi, bạn có thể cần sao chép cùng một bảng nhiều lần, ví dụ để kiểm tra các công thức khác nhau trên cùng một tập dữ liệu. Điều này có thể dễ dàng thực hiện với macro sau.

Public Sub DuplicateSheetMultipleTimes()
        Dim n As Integer
        On Error Resume Next
        n = InputBox("How many copies of the active sheet do you want to make?")

        If n >= 1 Then
                For numtimes = 1 To n
                        activeSheet.Copy After:=ActiveWorkbook.Sheets(Worksheets.Count)
                Next
        End If
End Sub

Mở trang tính gốc, chạy macro, chỉ định số lượng bản sao của trang hoạt động bạn muốn tạo và nhấp vào đồng ý:
VBA Excel để nhân đôi trang tính nhiều lần

Cách nhân đôi trang tính trong Excel với VBA

Để sao chép một trang tính trong Excel bằng một trong các macro ở trên, bạn có thể chèn mã VBA vào sách của mình hoặc chạy macro từ chúng tôi sách bài tập mẫu.

Cách thêm macro vào sổ làm việc của bạn

Để chèn mã vào sổ làm việc của bạn, hãy thực hiện các bước sau:

Mở bảng tính bạn muốn sao chép.
nhấn Alt + F11 để mở Trình soạn thảo Visual Basic.
Trên khung bên trái, nhấp chuột phải Cuốn sách nàyvà sau đó nhấp Chèn > Mô-đun.
Dán mã trong cửa sổ Mã.
nhấn F5 để chạy macro.

Để biết hướng dẫn chi tiết từng bước, vui lòng xem Cách chèn mã VBA trong Excel.

Cách chạy macro từ sổ làm việc mẫu của chúng tôi

Ngoài ra, bạn có thể tải xuống sổ làm việc mẫu của chúng tôi để Bản sao Excel và chạy mã từ đó.

Sổ làm việc mẫu chứa các macro sau:

CopySheetToNewWorkbook – sao chép bảng tính hiện tại vào một bảng tính mới.

CopySelectedSheets – sao chép nhiều trang tính mà bạn chọn vào một sổ làm việc mới.

CopySheetToBeginningAntherWorkbook – sao chép bảng hoạt động vào đầu một sổ làm việc khác.

CopySheetToEndAntherWorkbook – sao chép trang tính hoạt động vào cuối tệp Excel khác.

CopySheetAndRename – sao chép trang tính hiện tại, đổi tên trang tính theo chỉ định của người dùng và đặt bản sao sau tất cả các trang tính khác trong sổ làm việc hiện tại.

CopySheetAndRenamePred xác định – sao chép trang tính đang hoạt động, đặt tên mã hóa cứng cho bản sao và đặt nó ở cuối sổ làm việc hiện tại.

CopySheetAndRenameByCell – tạo một bản sao của trang hoạt động và đổi tên dựa trên giá trị ô đã chọn.

CopySheetAndRenameByCell2 – sao chép trang tính hoạt động và đổi tên dựa trên địa chỉ ô được mã hóa cứng.

CopySheetToClosesWorkbook – cho phép bạn sao chép trang tính vào một sổ làm việc đã đóng.

CopySheetFromClosesWorkbook – cho phép bạn sao chép một trang tính từ một tệp Excel khác mà không cần mở nó.

Sao chépSheetMult MônTimes – cho phép bạn nhân đôi một trang tính trong Excel nhiều lần.

Để chạy macro trong Excel của bạn, chỉ cần làm như sau:

Mở sổ làm việc đã tải xuống và kích hoạt nội dung nếu được nhắc.
Mở sổ làm việc của riêng bạn và điều hướng đến trang tính bạn muốn sao chép.
Trong bảng tính của bạn, nhấn Alt + F8, chọn macro quan tâm và nhấp Chạy.

Sao chép một trang tính trong Excel bằng cách sử dụng macro.

Đó là cách bạn có thể sao chép một trang tính trong Excel bằng VBA. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!

Bạn cũng có thể quan tâm

Source link

The post Cách sao chép một trang tính trong Excel với VBA appeared first on Kế Toán MVB | Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng.

source https://ketoanmvb.com/cach-sao-chep-mot-trang-tinh-trong-excel-voi-vba.html

Ketoanmvb - Dịch vụ kế toán, quyết toán thuế uy tín tại Hà Nội

Thứ Ba, 14 tháng 7, 2020