Excel中工作表间数据复制方法（含动态更新表日志留存需求）

Hey there! Let's tackle your two Excel data copying scenarios—super common needs, so I'll walk you through both basic and advanced solutions clearly.

Here are three straightforward methods depending on what you need:

• 手动复制粘贴（最直观）

  1. 切换到源工作表，选中要复制的区域（press Ctrl+A to select everything if needed）
  2. Hit Ctrl+C to copy, then switch to your target worksheet. Click the starting cell where you want the data to go, and press Ctrl+V to paste. Want to keep formatting? Right-click and pick "Keep Source Formatting" from the paste options.

• 公式引用（同步静态数据）
  In the target worksheet's cell, type =源工作表名称!单元格地址—for example, =Sheet1!A1. Drag the fill handle (the small square at the cell's bottom-right) to copy this formula across the entire range you need.
  Pro tip: If you want to lock in the data at a specific moment (so it doesn't update when the source changes), copy the formula cells, right-click, and choose "Paste Values".

• 整表复制（快速生成副本）
  Right-click the source worksheet's tab (at the bottom of the Excel window, like Sheet1), select "Move or Copy". In the popup, choose the target workbook (pick your current file if you're working in one book), check the "Create a copy" box, and click OK. You'll get an exact duplicate of the entire sheet.

If your source sheet updates regularly and you need to keep a historical log of every state, VBA is the way to go—it automatically captures changes without you having to remember to copy manually.

Here's a step-by-step implementation:

Step 1: Set up the VBA code #

1. Open your Excel file, press Alt+F11 to open the VBA Editor.
2. In the left "Project Explorer" pane, find your workbook, then double-click the source worksheet (name it something like 动态数据 if you haven't already).
3. Paste this code into the right-hand code window:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Define worksheet objects
    Dim sourceSheet As Worksheet
    Dim logSheet As Worksheet
    Dim lastSourceRow As Long
    Dim lastLogRow As Long
    Dim timestamp As String
    
    ' Assign your sheet names here (match your actual sheet names!)
    Set sourceSheet = ThisWorkbook.Worksheets("动态数据")
    Set logSheet = ThisWorkbook.Worksheets("数据日志")
    
    ' Create a timestamp for the log entry
    timestamp = Format(Now(), "yyyy-mm-dd hh:mm:ss")
    
    ' Find the last row with data in the source sheet
    lastSourceRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    ' Find the next empty row in the log sheet to append data
    lastLogRow = logSheet.Cells(logSheet.Rows.Count, "A").End(xlUp).Row + 1
    
    ' Copy all data from source to log sheet
    sourceSheet.Range("A1:" & sourceSheet.Cells(lastSourceRow, sourceSheet.Columns.Count).Address).Copy _
        Destination:=logSheet.Range("A" & lastLogRow)
    
    ' Add the timestamp in the next column after the copied data
    logSheet.Cells(lastLogRow, sourceSheet.Columns.Count + 1).Value = "更新时间: " & timestamp
    
    ' Clear the clipboard to avoid annoying paste prompts
    Application.CutCopyMode = False
End Sub

Step 2: Customize for your needs #

• If your source sheet has a header row and you don't want to repeat it in the log, change sourceSheet.Range("A1:" to sourceSheet.Range("A2:"—just make sure your log sheet already has the header row manually added.
• This code triggers every time any cell in the source sheet is edited. If you prefer to capture data on a schedule (e.g., every hour), you can use a timed macro with Application.OnTime, but the change-triggered version is usually more useful for logs.

Step 3: Enable macros #

Save your file as a .xlsm (Macro-Enabled Workbook)—otherwise the code won't run. When you open the file, enable macros when prompted.

内容的提问来源于stack exchange，提问作者Karthikeyan