Excel VBA脚本有条件地填充另一个工作表

1 回答

• 0

  我有一个代码，当列的 Headers 是某个字符串时复制列，这会有帮助吗？
  Edit1:
  这就是我想出的 . 代码应该足够灵活，以适应您所拥有的任何类型的电子表格

  Dim keyColumn As Integer
  Dim i As Integer
  Dim keyWord As Variant 'I've used variant, so you can choose your own data type for the keyword
  Dim dataSh As String 'I'm using sheet names for sheet referencing
  Dim populateSh As String
  Dim rowNum As Integer
  Dim dataRow() As Variant
  
  Sub Populate()
  'set the column number, which contains the keywords, the keyword itself,
  'name of the sheet to populate and the row offset you'd like to start populating
      populateSh = "populate"
      keyColumn = 4
      keyWord = "yes"
      rowNum = 0
  'assuming you run the macro in the sheet you get the data from, get its name to return to it after copying the row
      dataSh = ActiveSheet.Name
  'loop through all the used cells in the column
      For i = 1 To ActiveSheet.UsedRange.Rows.Count
          If Cells(i, keyColumn) = keyWord Then
  'starting in row 1 in the sheet you populate, you'll have to set the rowNum variable to desired offset few lines above
              rowNum = rowNum + 1
              Call copyRow(i, rowNum)
          End If
      Next i
  End Sub
  
  Sub copyRow(ByVal cRow As Integer, ByVal pRow As Integer)
      Dim colNum As Integer
  'set the number of columns you'd like to copy
      colNum = 3
  'redimension the array to carry the data to other sheet
  'this can be done any way you,d like, but I'm using array for flexibility
      ReDim dataRow(1 To colNum)
  'put the data into the array, as an example I'm using columns 1, 2 and 3, while skipping the keyword column.
      dataRow(1) = Cells(cRow, 1)
      dataRow(2) = Cells(cRow, 2)
      dataRow(3) = Cells(cRow, 3)
  
      Sheets(populateSh).Select
          For p = 1 To UBound(dataRow)
              Cells(pRow, p) = dataRow(p)
          Next p
      Sheets(dataSh).Select
  End Sub
  

  希望有所帮助 . 对不起任何样式错误提前

  回复于 2024-04-19T22:45:18+08:00