通过VBA复制行时Excel崩溃 [英] Excel Crash When Copying row through VBA
问题描述
Excel VBA上的新手.我只想问一下如何解决我遇到的错误?我正在尝试根据条件复制整个行,然后将其粘贴到另一张纸上(相同的工作簿).这是我的代码:
Newbie here on Excel VBA. I would just like to ask how to fix the error I'm getting? I'm trying to copy an entire row base on a criteria, and paste it on another sheet (same workbook). Here's my code:
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim endRow As Range
Set ws = Sheets("FIELD OFFICE DATABASE")
Set ws1 = Sheets("Transferred Items")
set endRow = Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
ws.Activate
ws.Unprotect "321321"
Range("B2").Select
Do Until endRow
If ActiveCell.Value = Me.cmbemn.Text Then
ActiveCell.Rows("B:S").Select
Selection.copy
ws1.Activate
ws1.Unprotect "321321"
endRow.Select
ActiveSheet.Paste
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
发生的情况是我的Excel崩溃了,还是弹出了一个错误消息,提示需要对象".但是大多数情况下它崩溃了.
What happens is its either my Excel crashes, or an error pops up saying "Object required". But mostly it crashes.
请帮助!谢谢!
推荐答案
尝试将循环终止条件更改为类似以下内容:
Try changing the loop termination condition to something like:
Do Until ActiveCell.Address = endRow.Address
我认为您可以提前终止:
I think you could terminate earlier with:
Set endRow = ws.Range("B" & Rows.Count).End(xlUp)
您还应该明确说明您正在使用哪个工作表,否则将使用当前的活动表.我假设您正在使用 ws
.
You also should be explicit in which worksheet you are working with or else the currently activesheet will be used. I have assumed you are working with ws
.
并且 Rows
不应在其中指定列:
And Rows
should not specify columns within it:
ActiveCell.Rows("B:S").Select
它应该具有Rows规范.可能您可以执行以下操作:
It should have a Rows specification. Potentially you could do something like:
ActiveCell.EntireRow.Copy
或根据以后的评论
currCell.EntireRow.Copy
不需要.选择
,然后选择第二行.您可以一行完成.
There doesn't need to be a .Select
then a second line for the copy. You can do in one line.
还请注意@Jeeped对 Unprotect
的评论.
Note also @Jeeped's comment on Unprotect
.
我的偏好是指定例如loopRange
My preference would be to specify a loopRange e.g.
Set loopRange = ws.Range("B2:B" & ws.Range("B" & Rows.Count).End(xlUp).Row)
,然后在该范围内循环.
and then loop over that range.
Dim currCell As Range
For Each currCell in loopRange
If currCell.Value .....do stuff
Next currCell
这具有摆脱使用主动单元的额外好处.
This has the added benefit of getting rid of the use of activecell.
这篇关于通过VBA复制行时Excel崩溃的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!