用VBA粘贴同一行但不同列的复制数据

问题描述：

通过我先前提出的问题，新的主题诞生了。现在，我有几个复制的单元格，我想将它们转换为初始文件（例如宏所在的ThisWorkbook）。用VBA粘贴同一行但不同列的复制数据

我试过的是保持块运算符“FOR”的同一行，将光标移回少数列并粘贴到那里。但是出现了一个错误。当我使用统计方式“范围（”C10“）”，但每次程序必须有不同的单元格粘贴在那里。那么，我该如何应对呢？

预先感谢您！

Option Explicit 

Sub FileFinder() 
' Excel variables: 
Dim wbResults, oWB As Workbook 
Dim Sht As Worksheet 
Dim RngS As Range 
Dim sDir As String 
Dim LastRow, i, Col As Long 
'Optimizing CPU speed 
Application.ScreenUpdating = False 
Application.EnableEvents = False 
Application.Calculation = xlCalculationManual 
' set the worksheet object 
Col = 25 
Set Sht = Worksheets("Accounts source data") 
With Sht 
    ' find last row with data in Column "Y" (Col = 25) 
    LastRow = .Cells(.Rows.Count, 25).End(xlUp).Row 
    For i = 3 To LastRow 
     If .Cells(i, Col) = "In Scope" Then 
      ' Set the range directly, no need to use `Select` and `Selection` 
      Set RngS = .Cells(i, Col).Offset(, -22) 
      ' Search, in same directory where the file is located, the file with that account (file comes with account number as name) 
      sDir = Dir$(ThisWorkbook.Path & "\" & RngS.Value & ".xlsx", vbNormal) 
      Set oWB = Workbooks.Open(ThisWorkbook.Path & "\" & sDir) 
      oWB.Worksheets("Report").Range("B27:B30").Copy 

      'My error appears here: Run-time Error 424: Object required 
      'If I replace "Cells(i, Col).Offset(, -11)" from below with "Range("C10")" the code works perfectly. But this is not the desired result 
      wbResults.Worksheets("Accounts source data").Cells(i, Col).Offset(, -11).PasteSpecial Paste:=xlPasteAll, Transpose:=True 
      oWB.Close SaveChanges:=False 

      ' clear objects 
      Set RngS = Nothing 
      Set oWB = Nothing 
     End If 

    Next i 
End With 
'End optimizing CPU speed 
Application.Calculation = xlCalculationAutomatic 
Application.EnableEvents = True 
Application.ScreenUpdating = True 
End Sub

您需要特别使用'Dim wbResults As Workbook'，因为'Dim wbResults，oWB As Workbook'意味着'wbResults'是'Variant'。你也从来没有'设置wbResults = ThisWorkbook'。 –

答

只是一个快速修复。试试这样：

ThisWorkbook.Worksheets("Accounts source data").Cells(i, Col).Offset(, -11).PasteSpecial Paste:=xlPasteAll, Transpose:=True

如果它的工作原理，然后尝试设置wbResults到ThisWorkbook，如在评论中提到。

您需要设置wbResults到指定的工作簿，如评论propsed。 Set wbResults = ThisWorkbook是一种可行的方法。此外，在VBADim a,b,c as Long设置的最后一个值为Long另外两个设置为Variant。

用VBA粘贴同一行但不同列的复制数据

相关推荐