从其他工作表复制已填充单元格并定位首空行的VBA实现问题

Hey there! Let's work through this VBA problem step by step since you're new to it. First, let's break down what's not working with your current code:

• Your code is copying data from Sheet1 to Dana instead of the direction you need (Dana → Sheet1)
• It only handles a single column instead of the 7 columns you mentioned
• It uses fixed row ranges, which doesn't account for dynamic row counts or finding the first empty cell in Sheet1's B column automatically

Here's the corrected code that meets your exact requirement:

Private Sub CommandButton1_Click()
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim lastRowSource As Long, firstEmptyRowTarget As Long
    
    ' Set stable references to your worksheets (no more relying on active workbooks)
    Set wsSource = ThisWorkbook.Sheets("Dana")
    Set wsTarget = ThisWorkbook.Sheets("Sheet1")
    
    ' Find the last row with data in Dana's A column (works for 1-1000 rows dynamically)
    lastRowSource = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    
    ' Find the first empty row in Sheet1's B column
    firstEmptyRowTarget = wsTarget.Cells(wsTarget.Rows.Count, "B").End(xlUp).Row + 1
    
    ' Copy all 7 columns (A to G) from Dana to the empty spot in Sheet1's B column
    wsSource.Range("A1:G" & lastRowSource).Copy Destination:=wsTarget.Range("B" & firstEmptyRowTarget)
    
    ' Optional: Uncomment this line if you want to CUT instead of copy (clear Dana's data after)
    ' wsSource.Range("A1:G" & lastRowSource).ClearContents
End Sub

Quick breakdown of what each part does: #

• Worksheet References: Using ThisWorkbook ensures we always target the workbook with your code, even if other Excel files are open.
• Dynamic Last Row: The line lastRowSource = ... automatically finds the bottom of your data in Dana's A column, so you don't have to hardcode row numbers.
• First Empty Target Row: This line jumps to the bottom of Sheet1's B column, moves up to the last filled cell, then adds 1 to get the first empty spot.
• Copy 7 Columns: Grabs all data from A to G in Dana and pastes it directly into Sheet1's B column starting at the empty row.

How to apply this: #

1. Open your workbook
2. Right-click the ActiveX CommandButton on Sheet1
3. Select View Code
4. Replace your existing code with the code above
5. Save the workbook as a Macro-Enabled Workbook (.xlsm) if it isn't already

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