说明 - 使用PasteSpecial复制数据VBA代码

Question

我有这两个工作簿。

欲某些列的数据在第一个工作簿中工作簿2.

我正在运行时（1004）的误差传输到一个给定的片材。 “对象范围”的方法失败的工作表

我收到错误在此行中的代码

 SummarySheet.Range(columnaddress & "8:" & columnaddress & lastrow).Copy

下面是完整的代码。

Sub RectangleRoundedCorners1_Click() 

    Application.ScreenUpdating = False 

    Dim MacroBook As Workbook, SummaryBook As Workbook, BSSBook As Workbook, _ 
    SummarySheet As Worksheet, TwoGonlyER As Worksheet, threeGswapRoll As Worksheet, _ 
    threeGswapER As Worksheet, fourGprData As Worksheet, threeGpRData As Worksheet 
    Dim onlyER As Range, rolloutER As Range, swapER As Range, prData As Range, _ 
    gPRdata As Range, FolderPathForSummary As String, lastrow As Integer 

    Set MacroBook = ThisWorkbook 

    'Summary Book Open 
    FolderPathForSummary = MacroBook.Worksheets("Tool").Range("D8") 

    FolderPath = MacroBook.Worksheets("Tool").Range("D11") 

    If Dir(FolderPath, vbDirectory) = "" Then 
    MsgBox "Please enter a valid folder path to save the file!" 
    Exit Sub 
    End If 

    If Not Dir(FolderPathForSummary) <> "" Then 
    MsgBox " Summary file doesn't exist in your mentioned address. Please check again! " 
    Exit Sub 
    End If 

    Set SummaryBook = Workbooks.Open(FolderPathForSummary) 
    Set SummarySheet = SummaryBook.Worksheets("Summary Data") 

    lastrow = SummarySheet.Cells(Rows.Count, "A").End(xlUp).Row 

    'BSS Tracker Open 
    Set BSSBook = Workbooks.Add 
    BSSBook.SaveAs (FolderPath & "BSS Tracker " & Format(CStr(Now), "ddmmmyyyyhhmmss") & ".xlsx") 

    MacroBook.Worksheets("2G Only ER").Copy before:=BSSBook.Sheets(1) 
    Set TwoGonlyER = BSSBook.Worksheets("2G Only ER") 

    MacroBook.Worksheets("3G Swap & 4G ROllout ER").Copy before:=BSSBook.Sheets(1) 
    Set threeGswapRoll = BSSBook.Worksheets("3G Swap & 4G ROllout ER") 

    MacroBook.Worksheets("3G & 4G Swap ER").Copy before:=BSSBook.Sheets(1) 
    Set threeGswapER = BSSBook.Worksheets("3G & 4G Swap ER") 

    MacroBook.Worksheets("4G PR Data").Copy before:=BSSBook.Sheets(1) 
    Set fourGprData = BSSBook.Worksheets("4G PR Data") 

    MacroBook.Worksheets("3G PR Data").Copy before:=BSSBook.Sheets(1) 

    Set threeGpRData = BSSBook.Worksheets("3G PR Data") 
    Set swapER = threeGswapER.Range("A3:P3") 

    For Each entry In swapER 

    columnaddress = entry.Value 

    SummarySheet.Range(columnaddress & "8:" & columnaddress & lastrow).Copy 
    entry.PasteSpecial 

    Next entry 

    BSSBook.Save 
    BSSBook.Close 

    SummaryBook.Save 
    SummaryBook.Close 

    MsgBox "BSS Tracker successfully generated!" 

End Sub 

Answer 1

根据你的代码，columnaddress应该是字符串变量，它应该包含列字母。类似于

Dim columnaddress As String 
columnaddress = "A" 

还有其他一些变量，如SummarySheet，它引用源表单和引用目标表单的表单表单。

变量lastrow应该是一个很长的变量，它是指数据的最后一行。

所以，如果是LASTROW说25和columnaddress是“A”，则每以下行

SummarySheet.Range(columnaddress & "8:" & columnaddress & lastrow).Copy 

接着范围A8：从SummarySheet A25被复制并粘贴到条目表。