存在两个问题,首先,你需要使用code子(选择电池。 BUT,因为你从总经理中选择了整个行程,你可以把整个行程复制到B2,因为幅度与大小相同,因此,你需要选择第一组囚室(A2)。

因此,整个案件陈述应如此:

 Case ThisValue = "Termination "
        Sheets("Master").Cells(x, 2).EntireRow.Copy
        Sheets("Terminations").Activate
        Range("A2").Select
        ActiveSheet.Paste

若干问题

1. No need to Select, use variables instead
2. Dim all your variables - help with debugging and learning
3. Some general good practice techniques will help

页: 1

Public Sub moveToSheet()
    Dim wb As Workbook
    Dim shMaster As Worksheet, shHiring As Worksheet
    Dim rngMaster As Range
    Dim x As Long
    Dim rw As Range

    Set wb = ActiveWorkbook
    Set shMaster = wb.Worksheets("Master")
    Set shHiring = wb.Worksheets("Hiring")
      etc

      Find the data
    x = shMaster.UsedRange.Count    trick to reset used range
    Set rngMaster = shMaster.UsedRange
     Loop through each row  NOTE looping thru cells is SLOW.  There are faster ways
    For Each rw In rngMaster.Rows
          Decide where to copy based on column F
        Select Case Trim$(rw.Cells(1, 6).Value)    Is there really a space on the end?
            Case "Hiring"
                shHiring.[B2:W2500].Clear
                rw.Copy shHiring.[B2]
             Case   etc
        End Select
    Next rw

这正是我所谈论的。 我有一个“主人”表,有几千个行和100个列。 这一基本版本仅搜索Column Y,然后复制浏览。 由于其他人使用这一工具,但我有几张模版的工作表格,我仍然非常隐蔽,这样,如果你不希望使用模板,你就可以避开。 如果需要,我还可以增加额外的搜索变数,简单地在另一条线上增加足够容易。 因此,如果你想要复制与两个变数相匹配的行文,那么你就会界定另一个变数的<代码>。 缩略语 Set d = shtMaster.Range(“A1”),或你希望检索第二个变量的任何栏目。 然后改为<代码>。 如果c.Value = “XXX”和 d.Value = “YYY”)。 Set d = d.Offset (1,0) at the past with the other. 事实证明,这实际上对我来说非常灵活。

Sub CreateDeptReport(Extras As String)

    Dim shtRpt As Excel.Worksheet, shtMaster As Excel.Worksheet
    Dim LCopyToRow As Long
    Dim LCopyToCol As Long
    Dim arrColsToCopy
    Dim c As Range, x As Integer

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    On Error GoTo Err_Execute

    arrColsToCopy = Array(1, 3, 4, 8, 25, 25, 21, 16, 17, 15, 31, 7)  which columns to copy ?

    Set shtMaster = ThisWorkbook.Sheets("MasterSheet")
    Set c = shtMaster.Range("Y5")   Start search in Column Y, Row 5

    LCopyToRow = 10  Start copying data to row 10 in Destination Sheet

    While Len(c.Value) > 0
         If value in column Y equals defined value, copy to destination sheet
        If c.Value = “XXX” Then

             only create the new sheet if any records are found
            If shtRpt Is Nothing Then
                 delete any existing sheet
                On Error Resume Next
                ThisWorkbook.Sheets("Destination").Delete
                On Error GoTo 0
                ThisWorkbook.Sheets("Template").Visible = xlSheetVisible
                ThisWorkbook.Sheets("Template").Copy After:=shtMaster
                Set shtRpt = ThisWorkbook.Sheets(shtMaster.Index + 1)
                shtRpt.Name = "Destination"  rename new sheet to Destination
    ‘Optional Information; can edit the next three lines out - 
                Range("F1").Value = "Department Name"
                Range("F2").Value = "Department Head Name"
                Range("B3").Value = Date
                ThisWorkbook.Sheets("Template").Visible = xlSheetVeryHidden
            End If

            LCopyToCol = 1

            shtRpt.Cells(LCopyToRow, LCopyToCol).EntireRow.Insert shift:=xlDown

            For x = LBound(arrColsToCopy) To UBound(arrColsToCopy)

                shtRpt.Cells(LCopyToRow, LCopyToCol).Value = _
                            c.EntireRow.Cells(arrColsToCopy(x)).Value

                LCopyToCol = LCopyToCol + 1

            Next x            
            LCopyToRow = LCopyToRow + 1  next row
        End If
        Set c = c.Offset(1, 0)
    Wend

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    Range("A9").Select  Position on cell A9
    MsgBox "All matching data has been copied."
    Exit Sub

Err_Execute:
        MsgBox "An error occurred."
End Sub

Also, if you wanted then you could remove the screenupdating lines. As stupid as it sounds some people actually like to see excel working at it. With screenupdating off you don t get to see the destination sheet until the copying is completed, but with updating on the screen flickers like crazy because of it trying to refresh when each row is copied. Some of the older people in my office think that excel is broken when they can t see it happening so I keep screenupdating on most of the time. lol Also, I like having the templates because all of my reports have quite a few formulas that need to be calculated after the information is broken down so I am able to keep all the formulas where I want them with a template. Then all I have to do is run the macro to pull from the master sheet and the report is ready to go without any further work.