将数据复制并粘贴到excel中的其他工作表上

2 回答

• 0

  你的代码非常接近 . 事实上，它可能有效 . 我不确定 . 使用select和activate是不可避免的问题 . Avoid it. .

  这不是一个真正的答案 . 当你正在学习编程时，只需要一些思考 . 我的评论以 {-- 开头

  Option Explicit ' {--option explicit forces you to declare your variables. Do this. Always.
  
  Sub ticker()
  
      '{group your variable declarations together}
      Dim numberOfCellsToCopy As Integer
      Dim currentCopyColumn As Integer
      Dim currentPasteRow As Integer
      Dim i As Integer
      ' {--use a worksheet object instead of selecting}
      Dim srcWs As Worksheet
      Dim destWs As Worksheet
  
      'assign where to insert the columns
      currentCopyColumn = 2
      currentPasteColumn = 2
      numberOfCellsToCopy = 5
  
      ' {--Set worksheet objects}
      Set srcWs = ThisWorkbook.Worksheets("HistoricalDataRequest")
      Set destWs = ThisWorkbook.Worksheets("SomeOtherWorksheet")
  
      For i = 2 To numberOfCellsToCopy  'loop increases by 1??? {--YES. It does.}
          ' {--indent your code so it's easier to see program flow
          ' {--I have no idea what you're trying to do here.... sorry. You're only copying one cell
      '   --so I really can't figure what you're trying to accomplish}
  
          'Range(Cells(2, currentCopyColumn), Cells(2, currentCopyColumn)).Select 'number =row
          'Selection.Copy 'number =row
          'Range(Cells(7, currentPasteColumn), Cells(7, currentPasteColumn)).Select
          'ActiveSheet.Paste
  
          ' {--copy/paste can be done in one line using a range object
          '   --range uses a cell name.}
          srcWs.Range("B2").Copy Destination:=destWs.Range("B7")
  
          ' { --or more in line with your method like this}
          srcWs.Range(srcWs.Cells(2, currentCopyColumn)).Copy Destination:=destWs.Range(destWs.Cells(7, currentPasteColumn))
  
          'increase the column index values to go to the next one
          currentCopyColumn = currentCopyColumn + 1
          currentPasteColumn = currentPasteColumn + 4
  
      Next i ' {--next i will increment the i var for you}
  End Sub
  

  回复于 2024-05-20T13:24:15+08:00
• 0

  根据您编辑的代码，这应该工作：

  Sheets("Sheet5").Range("C4:C8").Value = Application.WorksheetFunction.Transpose(Sheets("Sheet2").Range("C2:G2").Value)
  

  由于您的阵列处于不同的方向，因此转置功能将使它们正确对齐 .

  还有其他一些方法可以做到这一点，但这可能是最直接的（如果您的范围不需要太大的灵活性） .

  回复于 2024-05-20T13:24:15+08:00