Excel VBA：当范围地址存储在变量中时如何复制粘贴单元格范围？

Hey there! As a fellow VBA user who's been down this road before, let's get your copy-paste working and clean up that code a bit—those Select/Activate calls are going to cause headaches later, trust me.

First, your core issue is you're not concatenating the variable with your range string. When you write Range("A1:LastTableCell"), VBA treats LastTableCell as part of the literal string instead of using the address value you stored. You need to use & to combine static text with your variable.

But even better: you don't need to store cell address strings at all—directly storing Range objects is safer and more efficient. Let's tackle both your immediate problem and a full code optimization.

Fixing Your Direct Copy-Paste Issue #

If you want to stick with storing address strings, here's the corrected syntax:

' Concatenate A1 with your LastTableCell address to form the full range
Worksheets("TableSheet").Range("A1:" & LastTableCell).Copy
' Paste to the address stored in PasteToHere
Worksheets("PasteSheet").Range(PasteToHere).PasteSpecial xlPasteValues ' Use this for values only, or .Paste for full copy

Or skip the clipboard entirely (faster and more stable):

Worksheets("PasteSheet").Range(PasteToHere).Resize(TableRows, TableColumns).Value = _
    Worksheets("TableSheet").Range("A1:" & LastTableCell).Value

Optimized Full Code (No More Select/Activate!) #

Your original code relies heavily on Select and Activate, which is a common beginner habit but leads to slow, error-prone code (it breaks if the user clicks another sheet mid-run). Let's rewrite it to directly reference objects instead:

Option Explicit
Sub CopyandPaste2()
    ' Declare variables with clear, specific types
    Dim wsTable As Worksheet
    Dim wsPaste As Worksheet
    Dim lastPasteRow As Long
    Dim tableRowCount As Long
    Dim tableColCount As Long
    Dim pasteStart As Range
    Dim sourceRange As Range
    
    ' Set worksheet objects once to avoid repeated lookups
    Set wsTable = ThisWorkbook.Worksheets("TableSheet")
    Set wsPaste = ThisWorkbook.Worksheets("PasteSheet")
    
    ' Find the last non-empty row in PasteSheet's column A
    lastPasteRow = wsPaste.Cells(wsPaste.Rows.Count, "A").End(xlUp).Row
    ' Set the starting cell for pasting (next empty row in column A)
    Set pasteStart = wsPaste.Cells(lastPasteRow + 1, "A")
    
    ' Find total rows/columns in your source table
    tableRowCount = wsTable.Cells(wsTable.Rows.Count, "A").End(xlUp).Row
    tableColCount = wsTable.Cells(1, wsTable.Columns.Count).End(xlToLeft).Column
    
    ' Define the full range to copy (A1 to last row/column of the table)
    Set sourceRange = wsTable.Range(wsTable.Cells(1, 1), wsTable.Cells(tableRowCount, tableColCount))
    
    ' Copy values directly (no clipboard needed)
    pasteStart.Resize(sourceRange.Rows.Count, sourceRange.Columns.Count).Value = sourceRange.Value
    
    ' Uncomment below if you need to copy formats/other attributes:
    ' sourceRange.Copy
    ' pasteStart.PasteSpecial xlPasteAll ' Or specify type like xlPasteValuesAndNumberFormats
    ' Application.CutCopyMode = False ' Clear clipboard
    
    MsgBox "Copy-paste completed successfully!"
End Sub

Key Takeaways for New VBA Users #

1. Avoid Select/Activate: Directly referencing Worksheet and Range objects is faster and prevents context errors if the user interacts with Excel while the macro runs.
2. Use Range Objects Instead of Strings: Storing a Range object (like sourceRange above) keeps track of which worksheet the range belongs to, eliminating confusion.
3. Quickly Find Last Rows/Columns: Cells(Rows.Count, "A").End(xlUp).Row is the standard, efficient way to find the last non-empty row in a column—way better than looping through cells.

Hope this fixes your problem! Ditching Select early will make your VBA code much more robust as you learn.

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