开发宏功能: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