EXCEL VBA代码，实现点击Sheet1按钮控件保存不连续单元格的数据到Sheet2中，然后清空输入内容

 

 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
View Code

 

posted @ 2016-04-20 14:20  linyanyao  阅读(1955)  评论(0)    收藏  举报