如何使用vba將資料從水平復制和粘貼到垂直-有解無憂

我正在嘗試從 sheet1 到 sheet3 復制并粘貼大量 200 個資料，例如：我有一個包含 600 個代碼的串列，代碼將做什么，它將從作業表 1.cell 復制前 200 個代碼（“C6 到 GT7”）并將其垂直粘貼到 sheet3 單元格 A2 中，我需要的是下一批 200 應該在第 201 行之后附加到表 3 中，但我當前的代碼只粘貼表中的最后 200 個代碼3.

如何使用vba將資料從水平復制和粘貼到垂直

 Sub getbulkprices()
    Application.ScreenUpdating = False
    
    Dim wb As Workbook, ws, ws1 As Worksheet
    Dim r, iLastRow As Long, plr as long

    
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")
    Set ws1 = wb.Sheets("Sheet2")
    
    
     iLastRow = ThisWorkbook.Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
     ThisWorkbook.Sheets("Sheet1").Range("A2:A500").ClearContents
  
    ThisWorkbook.Sheets("Sheet3").Range("A2:B500000").ClearContents
     
       For r = 2 To ws1.Range("A" & Rows.Count).End(xlUp).Row Step 200
                ThisWorkbook.Sheets("Sheet1").Cells(2, 1).Resize(200).Value = _
                ws1.Cells(r, 1).Resize(200).Value
                
                
                ws.Range("C1").FormulaR1C1 = "=@RHistory(R2C1:R200C1,"".Timestamp;.Close"",""NBROWS:""&R2C2&"" INTERVAL:1D"",,""SORT:ASC TSREPEAT:NO CH:In;"",R[5]C)"
                Application.Run "EikonRefreshWorksheet"
                
                Application.Wait (Now   TimeValue("0:00:02"))
              
               
                 plr = ThisWorkbook.Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
               
                ws.Range("D6:IK7").Copy
               
ThisWorkbook.Sheets("Sheet3").Range("A2:B" & plr   1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
              
                Application.CutCopyMode = False
                Application.StatusBar = r & " / " & iLastRow - 1
            Next r
                
                
                
    End Sub

uj5u.com熱心網友回復：

考慮將Rows.Count限定為與作業中的限定符相同的作業表：.Cellsplr

plr = ThisWorkbook.Sheets("Sheet3").Cells( _
          ThisWorkbook.Sheets("Sheet3").Rows.Count, 1 _
      ).End(xlUp).Row

更好地將復制和粘貼放在With塊內以避免重復作業表：

For r = 2 To ... Step 200
    ...

    With ThisWorkbook.Sheets("Sheet3")
        plr = .Cells(.Rows.Count, 1).End(xlUp).Row
    
        ws.Range("D6:IK7").Copy
               
        .Range(.Cells(plr   1, 1), _ 
               .Cells(plr   200, 2) _
        ).PasteSpecial _
            Paste:=xlPasteValues, _
            Operation:=xlNone, _
            SkipBlanks:=False, _
            Transpose:=True
   End With

   ...
Next r

考慮均勻WorksheetFunction.Transpose并避免復制/粘貼：

With ThisWorkbook.Sheets("Sheet3")
    plr = .Cells(.Rows.Count, 1).End(xlUp).Row
          
    .Range(.Cells(plr   1, 1), _ 
           .Cells(plr   200, 2) _
    ) = WorksheetFunction.Transpose(ws.Range("D6:IK7"))
End With

uj5u.com熱心網友回復：

將粘貼更改為 ThisWorkbook.Sheets("sheet3").Range("A" & plr 1 & ":B" & plr 201).PasteSpecial...

轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/419965.html

標籤：

上一篇：基于兩列的日期順序中的第N次出現

下一篇：使用Python/Pandas提取與每個城市相關的資料，并使用回圈或函式保存在單獨的Excel表中