将一行从一个工作表复制/粘贴到另一个工作表会产生类型不匹配错误 [英] Copy/paste a row from one worksheet to another produces type mismatch error

问题描述

此宏用于根据F列中的条件将记录从主表移动到其他表.

This macro is to move records from a master sheet to other sheets based on criteria from column F.

在选择"B2"单元格的终止"情况下，发生类型不匹配错误.

A type mismatch error occurs in the "Termination" case where it is selecting the cell "B2".

我尝试了几种不同的选项，但每个选项最终都有不同的错误.

I tried several different options, but each ends up with a different error.

Public Sub moveToSheet()

Sheets("Master").Select
' Find the last row of data
FinalRow = Range("E65000").End(xlUp).Row
'Loop through each row
For x = 2 To FinalRow
    ' Decide where to copy based on column F
    ThisValue = Range("F" & x).Value

    Select Case True

    Case ThisValue = "Hiring "
        Sheets("Master").Cells(x, 2).EntireRow.Copy
        Sheets("Hiring").Select
        Sheets("Hiring").Range("B2:W2500").Clear
        Sheets("Hiring").Cells("B2").Select
        ActiveSheet.Paste
        Sheets("Master").Select
    Case ThisValue = "Re-Hiring "
        Sheets("Master").Cells(x, 2).EntireRow.Copy
        Sheets("Hiring").Select
        Sheets("Hiring").Range("B2:W2500").Clear
        Sheets("Hiring").Cells("B2").Select
        ActiveSheet.Paste
    Case ThisValue = "Termination "
        Sheets("Master").Cells(x, 2).EntireRow.Copy
        Sheets("Terminations").Select
        Sheets("Terminations").Range("B2:W2500").Clear
        Sheets("Terminations").Cells("B2").Select
        ActiveSheet.Paste
    Case ThisValue = "Transfer "
        Sheets("Master").Cells(x, 2).EntireRow.Copy
        Sheets("Transfers").Select
        Sheets("Transfers").Range("B2:W2500").Clear
        Sheets("Transfers").Cells("B2").Select
        ActiveSheet.Paste
    Case ThisValue = "Name Change "
        Sheets("Master").Cells(x, 2).EntireRow.Copy
        Sheets("Name Changes").Select
        Sheets("Name Changes").Range("B2:W2500").Clear
        Sheets("Name Changes").Cells("B2").Select
        ActiveSheet.Paste
    Case ThisValue = "Address Change "
        Sheets("Master").Cells(x, 2).EntireRow.Copy
        Sheets("Address Changes").Select
        Sheets("Address Changes").Range("B2:W2500").Clear
        Sheets("Address Changes").Cells("B2").Select
        ActiveSheet.Paste
    Case Else
        Sheets("Master").Cells(x, 2).EntireRow.Copy
        Sheets("New Process").Select
        Sheets("New Process").Range("B2:W2500").Clear
        Sheets("New Process").Cells("B2").Select
        ActiveSheet.Paste
    End Select

Next x

End Sub

推荐答案

有两个问题，首先，您需要使用语法 Range("B2").Select 选择单元格.但，由于您已从母版表中选择了整行，因此您无法将整行复制到B2中，因为范围的大小不同，因此您需要选择第一个单元格(A2).

There are a couple problems, first, you need to use the syntax Range("B2").Select to select the cell. BUT, since you selected the entire row from the master sheet, you can't copy the entire row into B2, because the ranges aren't the same size, so you need to select the first cell (A2) instead.

因此，整个case语句应如下所示:

So, the entire case statement should look like this:

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

这篇关于将一行从一个工作表复制/粘贴到另一个工作表会产生类型不匹配错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！