计数行单元格并复制粘贴

Question

我使用我的代码来处理基于c#的宏软件但是我只想用VBA做宏，而不是用c#它能做到吗？不使用点？

1. B2~Bxxxxx中的数据
2. 我的c#程序复制B2单元格值并粘贴另一个工作表K3单元格
3. 在代码下运行宏

Sub CopyRows()

Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

Dim bottomL As Long
Dim x As Long
bottomL = Sheets("Total").Range("L" & Rows.Count).End(xlUp).Row: x = 1

Dim c As Range
Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row
For Each c In Sheets("Total").Range("K1:K" & bottomL)
If c.Value = "Inside" Then
c.EntireRow.Copy Worksheets("filter").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
x = x + 1
End If
Next c

End Sub

4. 然后我的c#程序选择b3并复制到其他工作表k3单元格，然后运行宏，然后循环该过程并在Bxxxxx上结束单元格

有人知道只使用VBA工作吗？

谢谢并为我糟糕的英语感到抱歉

Answer 1

在VBA中生成如下完整代码：

Function CopyRows()
Application.ScreenUpdating = False 
Application.EnableEvents = False 
ActiveSheet.DisplayPageBreaks = False
Dim bottomL As Long 
Dim x As Long 
bottomL = Sheets("Total").Range("L" & Rows.Count).End(xlUp).Row
x = 1
Dim c As Range Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row 
For Each c In Sheets("Total").Range("K1:K" & bottomL) 
If c.Value = "Inside" Then 
c.EntireRow.Copy 
Worksheets("filter").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) 
x = x + 1 
End If  
Next c
End Function 
Sub Main()
Dim bottomB As Long 
Dim y As Long 
bottomB = Range("B" & Rows.Count).End(xlUp).Row
For y = 2 To bottomB
Range("B" & 2).Copy Worksheets("Total").Range("K3")
CopyRows
Next
End Sub

然后只运行Sub-Main((。

Answer 2

谢谢Wasif Hasan

我已经使用这样的代码我做

Sub dual()
Application.ScreenUpdating = False ActiveSheet.DisplayPageBreaks = False
Dim i As Long
Dim totalRows As Long
Dim lastRow As Long
Dim Number As Long
Dim nowRows As Long
Dim bottomL As Long
Dim x As Long
Dim c As Range
Dim lr As Long

bottomL = Sheets("Total").Range("L" & Rows.Count).End(xlUp).Row: x = 1
lr = Cells(Rows.Count, 1).End(xlUp).Row
With Worksheets("List")
'for looping
totalRows = .Cells(.Rows.Count, "B").End(xlUp).Row
'index of row to add from
lastRow = totalRows + 1 '<--| start pasting values one row below the last non empty one in column "B"
'data starts at row #2
For i = 2 To totalRows
If .Cells(i, 2).Value > 0 Then
Worksheets("List").Cells(i, "B").Copy
Worksheets("Total").Range("K3").PasteSpecial Paste:=xlPasteValues
lastRow = lastRow + Number
For Each c In Sheets("Total").Range("L1:L" & bottomL)
If c.Value = "Inside" Then
c.EntireRow.Copy Worksheets("filter").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
x = x + 1
End If
Next c
End If
Next i
End With Application.ScreenUpdating = True ActiveSheet.DisplayPageBreaks = True End Sub

但是它在复制时丢失了许多数据；粘贴所以它需要等待粘贴完成所以我使用其他程序做等待粘贴有什么选择吗？Thnaks您的答案

Answer 3

如果不需要复制和粘贴，则尽量不要使用该命令。只使用cell1.Value=cell2.Value会更快。在您的情况下，您应该声明一个变量来计算b中的列总数。然后使用循环从b2一直到bx。示例：

将i调为整数
dim j为整数
j=3


对于i=2到totalCount

工作表.单元格(2，i(.Value=工作表2.单元格(11，j(
j=j+1

下一个i

在上面的2=列B和11=列K