"Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub If Intersect(Target, Range("A2:N" & Rows.Count)) Is Nothing Then Exit Sub If Target.Row < 3 Then Exit Sub Dim key As String key = Cells(Target.Row, 4).Value If key = "" Then Exit Sub Dim ws As Worksheet On Error Resume Next Set ws = Worksheets(key) On Error GoTo 0 If ws Is Nothing Then Set ws = Worksheets.Add(after:=Sheets(Sheets.Count)) ws.Name = key Sheets("汇总表").Range("$A2:$N2").Copy ws.Range("A1") Else Dim i As Long i = Application.Match(key, Sheets("汇总表").Range("D:D"), 0) Sheets("汇总表").Range("$A2:$N2").Offset(i - 1).Resize(1).Copy ws.Range("A1") End If End Sub"运行时提示运行时错误 "1004" 无法对合并单元格执行此操作怎么修改代码?
时间: 2023-12-10 19:42:17 浏览: 35
这段代码中涉及到了复制和粘贴操作,如果目标单元格所在的行或列是合并单元格,就会出现无法进行操作的错误。我们可以在复制时避免复制合并单元格,可以修改代码如下:
```
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Intersect(Target, Range("A2:N" & Rows.Count)) Is Nothing Then Exit Sub
If Target.Row < 3 Then Exit Sub
Dim key As String
key = Cells(Target.Row, 4).Value
If key = "" Then Exit Sub
Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets(key)
On Error GoTo 0
If ws Is Nothing Then
Set ws = Worksheets.Add(after:=Sheets(Sheets.Count))
ws.Name = key
' 避免复制合并单元格,只复制第一个单元格
Sheets("汇总表").Range("$A2:$A2,$B2:$N2").Copy ws.Range("A1")
Else
Dim i As Long
i = Application.Match(key, Sheets("汇总表").Range("D:D"), 0)
' 避免复制合并单元格,只复制第一个单元格
Sheets("汇总表").Range("$A2:$A2,$B2:$N2").Offset(i - 1).Resize(1).Copy ws.Range("A1")
End If
End Sub
```
修改后的代码在复制时只复制了合并单元格中的第一个单元格,就避免了无法进行操作的错误。
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![txt](https://img-home.csdnimg.cn/images/20210720083642.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)