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:

  1. Mở bảng tính bạn muốn sao chép.
  2. nhấn Alt + F11 để mở Trình soạn thảo Visual Basic.
  3. 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.
  4. Dán mã trong cửa sổ Mã.
  5. 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:

  1. Mở sổ làm việc đã tải xuống và kích hoạt nội dung nếu được nhắc.
  2. 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.
  3. 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

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *