如何将单元格内容追加至另一工作表？附备注历史留存需求场景

Hey there! Let's figure out how to save every single entry you add to your notes column by appending it to the notes history column—so you never lose a single note again. I’ll break down solutions for the most common tools you might be using:

Excel Solution #

Automated with VBA Macro #

If you want to skip manual copying/pasting, a simple VBA macro can handle the append automatically. Here’s how:

1. Open your Excel file, press Alt + F11 to open the VBA Editor.
2. Right-click your workbook in the Project pane > Insert > Module.
3. Paste this code (adjust column letters if your notes/notes history are in different columns):

Sub AppendNotesToHistory()
    Dim ws As Worksheet
    Dim lastRowNotes As Long
    Dim lastRowHistory As Long
    Dim i As Long
    
    ' Set this to your target worksheet (e.g., "Sheet1")
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Find last filled row in notes column (A in this example)
    lastRowNotes = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' Find last filled row in notes history column (B here)
    lastRowHistory = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    ' Loop through notes starting from row 2 (assuming row 1 is headers)
    For i = 2 To lastRowNotes
        ' Skip empty cells and avoid duplicates
        If ws.Cells(i, "A").Value <> "" And _
           WorksheetFunction.CountIf(ws.Range("B:B"), ws.Cells(i, "A").Value) = 0 Then
            lastRowHistory = lastRowHistory + 1
            ws.Cells(lastRowHistory, "B").Value = ws.Cells(i, "A").Value
            ' Optional: Add a timestamp to track when the note was saved
            ws.Cells(lastRowHistory, "C").Value = Now()
        End If
    Next i
End Sub

4. Close the editor, then run the macro by pressing Alt + F8, selecting AppendNotesToHistory, and clicking Run. You can also add a button to your worksheet to trigger it with one click!

Manual Method (No Code) #

If you prefer keeping it simple:

• Select the new 1-2 rows you added to the notes column
• Copy them with Ctrl + C
• Navigate to the last empty row in the notes history column
• Paste with Ctrl + V

Google Sheets Solution #

Automated with Apps Script #

Google Sheets lets you automate this with Apps Script, and you can even set it to run automatically when you edit the sheet. Here’s how:

1. Open your Google Sheet, click Extensions > Apps Script.
2. Replace the default code with this snippet (adjust column numbers as needed):

function appendNotesToHistory() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const notesCol = 1; // Column A for notes
  const historyCol = 2; // Column B for notes history
  const timestampCol = 3; // Optional column for timestamps
  
  const lastRowNotes = sheet.getLastRow();
  // Get the last filled row in history column
  const historyValues = sheet.getRange(historyCol + ":" + historyCol).getValues().filter(String);
  let lastRowHistory = historyValues.length + 1; // +1 for header row
  
  // Get all notes from row 2 onwards
  const notesValues = sheet.getRange(2, notesCol, lastRowNotes - 1, 1).getValues();
  
  notesValues.forEach((row) => {
    const note = row[0];
    // Skip empty notes and duplicates
    if (note !== "" && !historyValues.flat().includes(note)) {
      sheet.getRange(lastRowHistory, historyCol).setValue(note);
      sheet.getRange(lastRowHistory, timestampCol).setValue(new Date());
      lastRowHistory++;
      historyValues.push([note]); // Update to avoid re-checking
    }
  });
}

3. Save the script, then click Run to test it. To set up automatic triggers:
   • Click the clock icon (Triggers) in the left pane
   • Click Add Trigger, choose appendNotesToHistory as the function
   • Set the event source to "From spreadsheet" and event type to "On edit" or "Time-driven" (for daily runs)

Manual Method #

Same as Excel: select your new notes, copy, and paste into the last empty row of the notes history column.

SQL Database Solution #

If your data is stored in a database (like MySQL or PostgreSQL), you can use an INSERT query to append new notes to your history table. Here’s an example:

If notes and history are in the same table #

-- Replace `daily_notes` with your table name, adjust column names as needed
INSERT INTO daily_notes (notes_history)
SELECT notes
FROM daily_notes
WHERE notes IS NOT NULL 
AND notes NOT IN (SELECT notes_history FROM daily_notes WHERE notes_history IS NOT NULL);

If you have a separate history table #

-- Append new notes to the history table with a timestamp
INSERT INTO notes_history (content, created_at)
SELECT notes, NOW()
FROM daily_notes
WHERE notes IS NOT NULL
AND notes NOT IN (SELECT content FROM notes_history);

Pick the method that fits your workflow best—automated solutions save time if you update daily, while manual works great for quick, occasional updates!

内容的提问来源于stack exchange，提问作者Penna Apoorv Allu