1 Private Sub SaveAndClear()
2
3 Dim Header, Deatil, Order As Range
4 Dim lastrow1, lastrow2 As Long
5 Dim i As Integer
6
7 lastrow1 = Sheet1.[B65536].End(xlUp).Row '取得sheet1 B列末行向上第一个有值的行数
8
9 Worksheets("sheet1").Activate
10 Set Header = Range("C3, E3, G3, G4, C5:C9, E5:E9")
11 Set Deatil = Range(Cells(13, 2), Cells(lastrow1, 6))
12 Set Order = Union(Header, Deatil)
13
14
15 '保存
16 For i = 13 To lastrow1
17 With Worksheets("Sheet2")
18 lastrow2 = .[A65536].End(xlUp).Row + 1 '取得sheet2 A列末行向上第一个有值的行数
19 .Cells(lastrow2, 1) = Sheet1.[C3].Value
20 .Cells(lastrow2, 2) = Sheet1.[E3].Value
21 .Cells(lastrow2, 3) = Sheet1.[G3].Value
22 .Cells(lastrow2, 4) = Sheet1.[G4].Value
23 .Cells(lastrow2, 5) = Sheet1.[C5].Value
24 .Cells(lastrow2, 6) = Sheet1.[C6].Value
25 .Cells(lastrow2, 7) = Sheet1.[C7].Value
26 .Cells(lastrow2, 8) = Sheet1.[C8].Value
27 .Cells(lastrow2, 9) = Sheet1.[C9].Value
28 .Cells(lastrow2, 10) = Sheet1.[E5].Value
29 .Cells(lastrow2, 11) = Sheet1.[E6].Value
30 .Cells(lastrow2, 12) = Sheet1.[E7].Value
31 .Cells(lastrow2, 13) = Sheet1.[E8].Value
32 .Cells(lastrow2, 14) = Sheet1.[E9].Value
33
34 .Cells(lastrow2, 15) = Sheet1.Cells(i, 2).Value
35 .Cells(lastrow2, 16) = Sheet1.Cells(i, 3).Value
36 .Cells(lastrow2, 17) = Sheet1.Cells(i, 4).Value
37 .Cells(lastrow2, 18) = Sheet1.Cells(i, 5).Value
38 .Cells(lastrow2, 19) = Sheet1.Cells(i, 6).Value
39 End With
40 Next
41
42 '删除
43 Order.ClearContents
44
45 End Sub
