开发宏功能:excel中从sheet批量插入

 

源数据如图:

 

宏操作:

 生成数据后:

 

 

 

 关键操作:在excel中启用开发工具,添加宏,然后添加模块即可,编辑完代码后,自定义功能按钮即可.

Sub MakeDataSource()

Dim isExistDestinationSheet
isExistDestinationSheet = False

Dim i

For i = 1 To Sheets.Count
If Sheets(i).Name = "b" Then
isExistDestinationSheet = True
End If
Next

If Not isExistDestinationSheet Then
If MsgBox("目标表不存在,将自动建Sheet:b", vbOKCancel) = vbOK Then
Sheets.Add after:=ActiveSheet
ActiveSheet.Name = "b"
Sheets("b").Select
ThisWorkbook.Worksheets("b").Range("a1") = "分类列表"
ThisWorkbook.Worksheets("b").Range("b1") = "名称列表"
ThisWorkbook.Worksheets("b").Range("c1") = "规格列表"
ThisWorkbook.Worksheets("b").Range("d1") = "添加时间"
ThisWorkbook.Worksheets("b").Rows(1).Font.Bold = True
ThisWorkbook.Worksheets("b").Rows(1).HorizontalAlignment = xlCenter
ThisWorkbook.Worksheets("b").Columns("d").NumberFormatLocal = "YYYY-MM-DD HH:MM:SS"

End If
End If


'复制插入
Dim iMinimumDestination, iMinimumSource
iMinimumDestination = ThisWorkbook.Worksheets("b").UsedRange.Rows.Count + 1
iMinimumSource = 2
If Len(Worksheets("a").Range("f3")) <> 0 Then

For i = iMinimumSource To iMinimumSource + 11
If Len(ThisWorkbook.Worksheets("a").Columns("a").Rows(i).Value) <> 0 Then
ThisWorkbook.Worksheets("b").Columns("a").Rows(iMinimumDestination).Value = Worksheets("a").Range("f3")
ThisWorkbook.Worksheets("b").Columns("b").Rows(iMinimumDestination).Value = ThisWorkbook.Worksheets("a").Columns("a").Rows(i).Value
ThisWorkbook.Worksheets("b").Columns("c").Rows(iMinimumDestination).Value = ThisWorkbook.Worksheets("a").Columns("b").Rows(i).Value
ThisWorkbook.Worksheets("b").Columns("d").Rows(iMinimumDestination).Value = Now
iMinimumDestination = iMinimumDestination + 1
End If
Next
End If
End Sub