填写工作表从多个工作表中的所有数据

2 回答

• 1

  将来自单个工作簿中的多个表单的数据组合到单个工作表以与不同的程序一起使用是VBA非常适合的任务 .

  下面的代码是 heavily ，用于解释每个步骤中发生的情况，尽管 LastOccupiedRowNum 和 LastOccupiedColNum 函数不是 . 识别最后一个占用的行/列是VBA编程的基础，并在此处巧妙地解释：Error in finding last used cell in VBA

  简而言之，两个最重要的要点是Workbook.Worksheets属性和Range.Copy方法 .

  Option Explicit
  Public Sub CombineDataFromAllSheets()
  
      Dim wksSrc As Worksheet, wksDst As Worksheet
      Dim rngSrc As Range, rngDst As Range
      Dim lngLastCol As Long, lngSrcLastRow As Long, lngDstLastRow As Long
  
      'Notes: "Src" is short for "Source", "Dst" is short for "Destination"
  
      'Set references up-front
      Set wksDst = ThisWorkbook.Worksheets("Import")
      lngDstLastRow = LastOccupiedRowNum(wksDst) '<~ defined below
      lngLastCol = LastOccupiedColNum(wksDst) '<~ defined below
  
      'Set the initial destination range
      Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)
  
      'Loop through all sheets
      For Each wksSrc In ThisWorkbook.Worksheets
  
          'Make sure we skip the "Import" destination sheet!
          If wksSrc.Name <> "Import" Then
  
              'Identify the last occupied row on this sheet
              lngSrcLastRow = LastOccupiedRowNum(wksSrc)
  
              'Store the source data then copy it to the destination range
              With wksSrc
                  Set rngSrc = .Range(.Cells(2, 1), .Cells(lngSrcLastRow, lngLastCol))
                  rngSrc.Copy Destination:=rngDst
              End With
  
              'Redefine the destination range now that new data has been added
              lngDstLastRow = LastOccupiedRowNum(wksDst)
              Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)
  
          End If
  
      Next wksSrc
  
  End Sub
  
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  'INPUT       : Sheet, the worksheet we'll search to find the last row
  'OUTPUT      : Long, the last occupied row
  'SPECIAL CASE: if Sheet is empty, return 1
  Public Function LastOccupiedRowNum(Sheet As Worksheet) As Long
      Dim lng As Long
      If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
          With Sheet
              lng = .Cells.Find(What:="*", _
                                After:=.Range("A1"), _
                                Lookat:=xlPart, _
                                LookIn:=xlFormulas, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False).Row
          End With
      Else
          lng = 1
      End If
      LastOccupiedRowNum = lng
  End Function
  
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  'INPUT       : Sheet, the worksheet we'll search to find the last column
  'OUTPUT      : Long, the last occupied column
  'SPECIAL CASE: if Sheet is empty, return 1
  Public Function LastOccupiedColNum(Sheet As Worksheet) As Long
      Dim lng As Long
      If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
          With Sheet
              lng = .Cells.Find(What:="*", _
                                After:=.Range("A1"), _
                                Lookat:=xlPart, _
                                LookIn:=xlFormulas, _
                                SearchOrder:=xlByColumns, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False).Column
          End With
      Else
          lng = 1
      End If
      LastOccupiedColNum = lng
  End Function
  

  像你上面看到的那样使用宏录像机是开始学习VBA的好方法，但是当涉及到循环时你很快就会碰壁 .

  您可以在此4分钟的步行中看到此代码的运行情况，并了解有关所包含概念的更多信息：https://www.youtube.com/watch?v=vbC2lGLFXS0&feature=youtu.be

  回复于 2024-05-05T00:51:10+08:00
• 0

  尝试Sheet1单元格A1

  =query({Sheet2!A:Z; Sheet3!A:Z; Sheet4!A:Z}, "where Col1 <>'' ")
  

  如果col A保存数字数据，则将最后一部分更改为“Col1不为空” .

  看看这是否有效？

  回复于 2024-05-05T00:51:10+08:00