求excel 高手 用VBA/宏 实现将sheet1筛选后的内容粘贴至sheet2_百度...

发布网友 发布时间：2024-10-23 09:17

我来回答

共3个回答

热心网友 时间：2024-12-01 00:03

程序如下，希望你喜欢：
Sub Run_Sort_Copy()
On Error GoTo ErrExit
Dim strSheetName_1 As String, strSheetName_2 As String
Dim iClass As Long, iMaxRow As Long, iRow As Long, iNewRow As Long
Application.ScreenUpdating = False
strSheetName_1 = "Sheet1" '此处可修改表1名称
strSheetName_2 = "Sheet2" '此处可修改表2名称
Sheets(strSheetName_1).Select
Range("A1").CurrentRegion.Select
iMaxRow = Selection.Rows.Count
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range( _
"C2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:= _
xlSortNormal, DataOption2:=xlSortNormal

iRow = 2
iClass = Range("B2").Value
iNewRow = 1
GoSub CopyMain
GoSub CopyRange

iRow = 3
Do Until iRow > iMaxRow
If Range("B" & iRow).Value = iClass Then
GoSub CopyRange
Else
iClass = Range("B" & iRow).Value
iNewRow = iNewRow + 2
GoSub CopyMain
GoSub CopyRange
End If
iRow = iRow + 1
Loop
Application.CutCopyMode = False
Application.StatusBar = False
Application.ScreenUpdating = True
Exit Sub

ErrExit:
MsgBox Err.Description, vbCritical, "错误"
Application.CutCopyMode = False
Application.StatusBar = False
Application.ScreenUpdating = True
Exit Sub

CopyMain:
Application.StatusBar = "正在处理 " & iRow & " / " & iMaxRow & " 行"
Sheets(strSheetName_2).Select
Range("A" & iNewRow) = iClass
iNewRow = iNewRow + 1
Sheets(strSheetName_1).Select
Rows("1:1").Select
Selection.Copy
Sheets(strSheetName_2).Select
Range("A" & iNewRow).Select
Selection.Insert Shift:=xlDown
iNewRow = iNewRow + 1
Sheets(strSheetName_1).Select
Return

CopyRange:
Application.StatusBar = "正在处理 " & iRow & " / " & iMaxRow & " 行"
Rows(iRow & ":" & iRow).Select
Selection.Copy
Sheets(strSheetName_2).Select
Range("A" & iNewRow).Select
Selection.Insert Shift:=xlDown
iNewRow = iNewRow + 1
Sheets(strSheetName_1).Select
Return
End Sub

热心网友 时间：2024-12-01 00:04

直接粘贴QQ，在粘贴到sheet2 最笨的方法 呵呵

热心网友 时间：2024-12-01 00:05

你的这个办法太笨.给你个方法:
全选整个数据区域,然后按班级排序.再在每个班级前面手工添加标题行.
如果你确实需要用VBA解决分班排序问题,可以放一个完整的数据样本的图上来.