標題: [發問] 關於"在原資料表截取特定資料貼到新sheet的問題" [打印本頁]

---

作者: yagami12th    時間: 2012-2-26 17:17     標題: 關於"在原資料表截取特定資料貼到新sheet的問題"

因為寫的程式無法順利成功篩選後複製，都是變成空白，所以想請教大家。

附檔：[attach]9764[/attach]

資料說明：
附檔內excel裡的sheet1是聯電股價原始資料，主要目的是要篩選出"股價大於10"的資料將其複製到新增後的"pick"sheet

自已嘗試著做出來的程式碼如下：

Option Explicit
Dim Flag
Dim myRow As Integer
--------------------------------
Sub addsheet()
   
    Sheets.Add
    Sheets(1).Select
    ActiveSheet.Name = "pick"
End Sub
---------------------------------
Sub Choose(k)
If Worksheets("Sheet1").Cells(k, 5).Value > 10 Then
    Flag = 1
ElseIf Worksheets("Sheet1").Cells(k, 5).Value > 5 And Worksheets("Sheet1").Cells(k, 5).Value <= 10 Then
    Flag = 0
End If
End Sub
----------------------------------
Sub CopyPaste(L, M)
Dim myStr As String

    myStr = L & ":" & L
    Rows("20:20").Select
    Selection.Copy
    Sheets("pick").Select
'    myStr = a & ":" & "M"
    Range(myStr).Select
    ActiveSheet.Paste
End Sub
---------------------------------
Sub main2()
Dim i As Integer
Dim myRange As Range
Dim myCell

Set myRange = Application.InputBox("Choose the days", Type:=8) '幫我選取我要的東西

addsheet '執行上述新增工作表的程式，可以執行上面寫的好幾個副程式，可以讓每個程式分工合作，組合在一起
myRow = 1

For Each myCell In myRange '在我的myrange裡對每一個mycell，來做下面的事情
    i = myCell.Row
        
    Flag = 0 '不符合我的要求就跳到下一圈去看是否有符合
    Choose i '檢測第20行是否符合我設的條件
    If Flag = 1 Then
        CopyPaste i, myRow '符合設定條件，貼到新的工作表，因為不是只有第二十行，所以要寫迴圈
        myRow = myRow + 1
    End If
Next
End Sub

---

作者: Hsieh    時間: 2012-2-26 18:57

回復 1# yagami12th

你的敘述跟程式碼意思好像不一樣
如果只是篩選E欄資料大於10的資料貼到新工作表

1. Sub Main()
   
2. Dim Sh As Worksheet
   
3. Application.DisplayAlerts = False
   
4. For Each Sh In Sheets
   
5.   If Sh.Name = "Pick" Then Sh.Delete
   
6. Next
   
7. Application.DisplayAlerts = True
   
8. With Sheet1
   
9. If .FilterMode = True Then .ShowAllData
   
10. .Range("A1").CurrentRegion.AutoFilter 5, ">=10"
    
11. .UsedRange.SpecialCells(xlCellTypeVisible).Copy
    
12. With Sheets.Add
    
13. .Name = "Pick"
    
14. .Paste
    
15. Application.CutCopyMode = False
    
16. End With
    
17. .AutoFilterMode = False
    
18. End With
    
19. End Sub

複製代碼

---

作者: yagami12th    時間: 2012-2-26 20:07

回復 2# Hsieh

謝謝版大，我好像用出來了，但有一行一直除不了錯，如下綠字色體：
Option Explicit
Dim Flag
Dim myRow As Integer
Dim newSheet As String

Sub addsheetVer2()
Static Num As Integer

    Sheets.Add after:=Sheets(Sheets.Count) '指名引數，數該excel檔有幾個sheet放在最右邊
    Sheets(Sheets.Count).Select
    ActiveSheet.Name = "pick & num"
   
    Num = Num + 1
End Sub


Sub ChooseVer2(rowChoose, sheetName As String) '原先只有輸入列號，現在要加上工作表的名字


If Worksheets("sheetName").Cells(rowChoose, 5).Value > 10 Then
    Flag = 1 '只選取指定sheet的資料做篩選
Else
End If
End Sub

Sub CopyPasteVer2(rowCopy, rowPaste, copySheet As String, pasteSheet As String) 'copy the row rowcopy in sheet with name "2330"
Dim myStr As String                                                             'and paste to the row rowpaste in the  sheet "pick"

    Sheets("copySheet").Select

    myStr = rowCopy & ":" & rowCopy
    Rows(myStr).Select
    Selection.Copy
    Sheets("pastSheet").Select
    myStr = "A" & rowPaste
    Range(myStr).Select
    ActiveSheet.Paste

End Sub

Sub main3()
Dim i As Integer
Dim myRange As Range
Dim myCell
Dim mySheet As String


mySheet = InputBox("input the sheet name you analyze") '選擇工作表

Set myRange = Application.InputBox("Choose the days", Type:=8) '幫我選取我要篩選的範圍


addsheetVer2 '執行上述新增工作表的程式，每次增加的不一樣，可以執行上面寫的好幾個副程式，可以讓每個程式分工合作，組合在一起
myRow = 1

For Each myCell In myRange '在我的myrange裡對每一個mycell，來做下面的事情
    i = myCell.Row
   
   
    Flag = 0 '不符合我的要求就跳到下一圈去看是否有符合
    ChooseVer2 i, mySheet '檢測第20行是否符合我設的條件
    If Flag = 1 Then
        CopyPasteVer2 i, myRow, mySheet, newSheet '符合設定條件，貼到新的工作表，因為不是只有第二十行，所以要寫迴圈
        myRow = myRow + 1
    End If
   
Next
End Sub

---

作者: Hsieh    時間: 2012-2-26 20:12

回復 3# yagami12th

If Worksheets(sheetName).Cells(rowChoose, 5).Value > 10 Then
    Flag = 1 '只選取指定sheet的資料做篩選
Else
End If
End Sub

---

作者: yagami12th    時間: 2012-2-26 20:21

回復 4# Hsieh

一直停在這行除錯，無法執行，程式碼應該沒錯才對．．．

    If Worksheets(sheetName).Cells(rowChoose, 5).Value > 10 Then
     Flag = 1 '只選取指定sheet的資料做篩選
Else
End If
End Sub

---

作者: yagami12th    時間: 2012-2-26 20:42

回復 4# Hsieh
回版大，改成用loop迴圈同樣也出現錯誤，試不出原因，除錯視窗停在下面綠色字體的地方

如附件：[attach]9774[/attach]

程式碼如下：
--------------------------------
Option Explicit
Dim Flag
Dim myRow As Integer
Dim newSheet As String
---------------------------------
Sub addsheetVer2()
Static Num As Integer

    Sheets.Add after:=Sheets(Sheets.Count) '指名引數，數該excel檔有幾個sheet放在最右邊
    Sheets(Sheets.Count).Select
    newSheet = "pick" & Num
    ActiveSheet.Name = newSheet
   
    Num = Num + 1
End Sub
----------------------------------------------

Sub ChooseVer2(rowChoose, sheetName As String) '原先只有輸入列號，現在要加上工作表的名字
If Worksheets("sheetName").Cells(rowChoose, 5).Value > 10 Then
    Flag = 1 '只選取指定sheet的資料做篩選
Else: the Flag = 0

End If
End Sub
-----------------------------------------
Sub CopyPasteVer2(rowCopy, rowPaste, copySheet As String, pasteSheet As String) 'copy the row rowcopy in sheet with name "2330"
Dim myStr As String                                                             'and paste to the row rowpaste in the  sheet "pick"

    Sheets("copySheet").Select

    myStr = rowCopy & ":" & rowCopy
    Rows(myStr).Select
    Selection.Copy
    Sheets("pastSheet").Select
    myStr = "A" & rowPaste
    Range(myStr).Select
    ActiveSheet.Paste

End Sub
-------------------------------------------
Sub main4()
Dim i As Integer
Dim mySheet As String

mySheet = InputBox("input the sheet name you analyze") '選擇工作表

addsheetVer2 '執行上述新增工作表的程式，每次增加的不一樣，可以執行上面寫的好幾個副程式，可以讓每個程式分工合作，組合在一起
myRow = 1: i = 2

Do While Worksheets(mySheet).Cells(i, 1) <> ""             '在指定的工作表裡第一行往下掃只要不是空白格就執行下面的程式碼
   
    Flag = 0 '不符合我的要求就跳到下一圈去看是否有符合
    ChooseVer2 i, mySheet '檢測是否符合我設的條件
    If Flag = 1 Then
        CopyPasteVer2 i, myRow, mySheet, newSheet '符合設定條件，貼到新的工作表，因為不是只有第二十行，所以要寫迴圈
        myRow = myRow + 1
    End If
    i = i + 1
Loop
End Sub

---

作者: yagami12th    時間: 2012-2-26 22:18

本帖最後由 yagami12th 於 2012-2-26 22:27 編輯

回復 4# Hsieh
用loop的檔案改成功了，原來是有些指定名稱不用用"" 包起來

如附件：[attach]9776[/attach]

Option Explicit
Dim Flag
Dim myRow As Integer
Dim newSheet As String

Sub addsheetVer2()
Static Num As Integer

    Sheets.Add after:=Sheets(Sheets.Count) '指名引數，數該excel檔有幾個sheet放在最右邊
    Sheets(Sheets.Count).Select
    newSheet = "pick" & Num
    ActiveSheet.Name = newSheet
   
    Num = Num + 1
End Sub


Sub ChooseVer2(rowChoose, sheetName As String) '原先只有輸入列號，現在要加上工作表的名字


If Worksheets(sheetName).Cells(rowChoose, 5).Value > 10 Then
    Flag = 1 '只選取指定sheet的資料做篩選


End If
End Sub



Sub CopyPasteVer2(rowCopy, rowPaste, copySheet As String, pasteSheet As String) 'copy the row rowcopy in sheet with name "2330"
Dim myStr As String                                                             'and paste to the row rowpaste in the  sheet "pick"

    Sheets(copySheet).Select

    myStr = rowCopy & ":" & rowCopy
    Rows(myStr).Select
    Selection.Copy
    Sheets(pasteSheet).Select
    myStr = "A" & rowPaste
    Range(myStr).Select
    ActiveSheet.Paste

End Sub

Sub main3()
Dim i As Integer
Dim myRange As Range
Dim myCell
Dim mySheet As String


mySheet = InputBox("input the sheet name you analyze") '選擇工作表

Set myRange = Application.InputBox("Choose the days", Type:=8) '幫我選取我要篩選的範圍


addsheetVer2 '執行上述新增工作表的程式，每次增加的不一樣，可以執行上面寫的好幾個副程式，可以讓每個程式分工合作，組合在一起
myRow = 1

For Each myCell In myRange '在我的myrange裡對每一個mycell，來做下面的事情
    i = myCell.Row
   
   
    Flag = 0 '不符合我的要求就跳到下一圈去看是否有符合
    ChooseVer2 i, mySheet '檢測第20行是否符合我設的條件
    If Flag = 1 Then
        CopyPasteVer2 i, myRow, mySheet, newSheet '符合設定條件，貼到新的工作表，因為不是只有第二十行，所以要寫迴圈
        myRow = myRow + 1
    End If
   
Next
End Sub

Sub main4()
Dim i As Integer
Dim mySheet As String


mySheet = InputBox("input the sheet name you analyze") '選擇工作表

addsheetVer2 '執行上述新增工作表的程式，每次增加的不一樣，可以執行上面寫的好幾個副程式，可以讓每個程式分工合作，組合在一起
myRow = 1: i = 2

Do While Worksheets(mySheet).Cells(i, 1) <> "" '在指定的工作表裡第一行往下掃只要不是空白格就執行下面的程式碼
   
    Flag = 0 '不符合我的要求就跳到下一圈去看是否有符合
    ChooseVer2 i, mySheet '檢測是否符合我設的條件
    If Flag = 1 Then
        CopyPasteVer2 i, myRow, mySheet, newSheet '符合設定條件，貼到新的工作表，因為不是只有第二十行，所以要寫迴圈
        myRow = myRow + 1
    End If
    i = i + 1
Loop
End Sub

---

作者: yagami12th    時間: 2012-2-26 22:49

回復 2# Hsieh
謝謝版大的程式碼，我的檔案如附件，有錯誤的程式碼為下面綠色字體：[attach]9777[/attach]

--------------------------------------------------------------------------------
Option Explicit
Dim Flag
Dim myRow As Integer
Dim newSheet As String

Sub addsheetVer2()
Static Num As Integer

    Sheets.Add after:=Sheets(Sheets.Count) '指名引數，數該excel檔有幾個sheet放在最右邊
    Sheets(Sheets.Count).Select
    ActiveSheet.Name = "pick & num"
   
    Num = Num + 1
End Sub
----------------------------------------------------------------

Sub ChooseVer2(rowChoose, sheetName As String) '原先只有輸入列號，現在要加上工作表的名字


If Worksheets(sheetName).Cells(rowChoose, 5).Value > 10 Then
    Flag = 1 '只選取指定sheet的資料做篩選
   

End If
End Sub
-------------------------------------------------------------------
Sub CopyPasteVer2(rowCopy, rowPaste, copySheet As String, pasteSheet As String) 'copy the row rowcopy in sheet with name "2330"
Dim myStr As String                                                             'and paste to the row rowpaste in the  sheet "pick"

    Sheets(copySheet).Select
    myStr = rowCopy & ":" & rowCopy
    Rows(myStr).Select
    Selection.Copy
    Sheets(pasteSheet).Select        <<<<<<<這裡出現陣列索引錯誤9，把冒號的錯誤更正還是一樣~~
    myStr = "A" & rowPaste
    Range(myStr).Select
    ActiveSheet.Paste

End Sub
--------------------------------------------------------------------
Sub main3()
Dim i As Integer
Dim myRange As Range
Dim myCell
Dim mySheet As String


mySheet = InputBox("input the sheet name you analyze") '選擇工作表

Set myRange = Application.InputBox("Choose the days", Type:=8) '幫我選取我要篩選的範圍


addsheetVer2 '執行上述新增工作表的程式，每次增加的不一樣，可以執行上面寫的好幾個副程式，可以讓每個程式分工合作，組合在一起
myRow = 1

For Each myCell In myRange '在我的myrange裡對每一個mycell，來做下面的事情
    i = myCell.Row
   
   
    Flag = 0 '不符合我的要求就跳到下一圈去看是否有符合
    ChooseVer2 i, mySheet '
    If Flag = 1 Then
        CopyPasteVer2 i, myRow, mySheet, newSheet '在指定的工作表作篩選後貼過去，符合設定條件，貼到新的工作表，因為不是只有第二十行，所以要寫迴圈
        myRow = myRow + 1
    End If
   
Next
End Sub

---

作者: Hsieh    時間: 2012-2-26 23:00

回復 8# yagami12th

沒有pasteSheet名稱的工作表

---

作者: GBKEE    時間: 2012-2-27 08:59

本帖最後由 GBKEE 於 2012-2-27 09:12 編輯

回復 8# yagami12th
用進階篩選 簡化你的程序

1. Sub Ex()
   
2. Dim MyRange As Range
   
3. On Error GoTo Sh_Add
   
4. Set MyRange = Application.InputBox("'選擇股票工作表資料任一範圍", Type:=8) '選取工作
   
5. Set MyRange = MyRange.CurrentRegion
   
6. With Sheets("pick & num")
   
7. .Cells.Clear
   
8. .[A1].Name = "CopyToRange"
   
9. .[IV1:IV2].Name = "Criteria"
   
10. .[IV1] = MyRange.Cells(1, 5)
    
11. .[IV2] = "="">=10"""
    
12. MyRange.AdvancedFilter xlFilterCopy, [Criteria], [CopyToRange], True
    
13. End With
    
14. Exit Sub
    
15. Sh_Add:
    
16. MsgBox Err
    
17. If Err.Number = 9 Then
    
18. With Sheets.Add(after:=Sheets(Sheets.Count))
    
19. .Name = "pick & num"
    
20. End With
    
21. End If
    
22. On Error GoTo 0
    
23. Resume
    
24. End Sub

複製代碼

---

作者: yagami12th    時間: 2012-2-27 09:20

回復 10# GBKEE

謝謝GBKEE大:D ，我再試試看。

---

作者: yagami12th    時間: 2012-2-27 12:04

本帖最後由 yagami12th 於 2012-2-27 12:07 編輯

回復 10# GBKEE
回GBKEE大，因為看範例vba引數的部份一直無法了解，就是下面程式碼紅色字體的部份，所以想請教GBKEE大關於引數的問題：

--------------------------------------------------------------------------------
Option Explicit
Dim Flag
Dim myRow As Integer
Dim newSheet As String

Sub addsheetVer2()
Static Num As Integer

    Sheets.Add after:=Sheets(Sheets.Count) '指名引數，數該excel檔有幾個sheet放在最右邊
     Sheets(Sheets.Count).Select
     ActiveSheet.Name = "pick & num"
     
    Num = Num + 1
End Sub
----------------------------------------------------------------

Sub ChooseVer2(rowChoose, sheetName As String) '原先只有輸入列號，現在要加上工作表的名字


If Worksheets(sheetName).Cells(rowChoose, 5).Value > 10 Then
     Flag = 1 '只選取指定sheet的資料做篩選
     

End If
End Sub
-------------------------------------------------------------------
Sub CopyPasteVer2(rowCopy, rowPaste, copySheet As String, pasteSheet As String) 'copy the row rowcopy in sheet with name "2330"
Dim myStr As String                                                             'and paste to the row rowpaste in the  sheet "pick"

    Sheets(copySheet).Select
     myStr = rowCopy & ":" & rowCopy
     Rows(myStr).Select
     Selection.Copy
     Sheets(pasteSheet).Select        <<<<<<<這裡出現陣列索引錯誤9，把冒號的錯誤更正還是一樣~~
     myStr = "A" & rowPaste
     Range(myStr).Select
     ActiveSheet.Paste

End Sub
-----------------------------------------
Sub main3()

Dim myRange As Range
Dim myCell, i
Dim mySheet As String


Set myRange = Application.InputBox("Choose the days", Type:=8) '幫我選取我要篩選的範圍
mySheet = InputBox("input the sheet name you analyze") '選擇工作表

addsheetVer2                            '執行上述新增工作表的程式，每次增加的不一樣myRow = 1

For Each myCell In myRange              '在我的myrange裡對每一個mycell，來做下面的事情
    Flag = 0                                 '不符合我的要求就跳到下一圈去看是否有符合
    i = myCell.Row                  
   
    ChooseVer2 i, mySheet                            '選取行檢測是否符合我設的條件
    If Flag = 1 Then                                             'choose子程序把flag改變
        CopyPasteVer2 i, myRow, mySheet, newSheet        
        myRow = myRow + 1
    End If
   
Next
End Sub

---

作者: GBKEE    時間: 2012-2-27 13:28

回復 12# yagami12th

1. Option Explicit
   
2. Dim Flag
   
3. Dim myRow As Integer
   
4. Dim newSheet As String
   
5. Private Const XpasteSheet = "pick & num"
   
6. 'Private Const "設為模組的私用常數  其值如字面所示    ***指定 貼上的工作表名稱
   
7. Sub addsheetVer2()
   
8.     Static Num As Integer
   
9.     On Error GoTo AD:
   
10.     With Sheets(XpasteSheet)
    
11.         .Cells.Clear
    
12.         Num = Num + 1
    
13.     End With
    
14.     Exit Sub
    
15. AD:
    
16.      Sheets.Add(after:=Sheets(Sheets.Count)).Name = XpasteSheet
    
17.      ''指名引數，數該excel檔有幾個sheet放在最右邊
    
18.     Resume    '返回程序錯誤處
    
19. End Sub
    
20. Sub ChooseVer2(rowChoose, sheetName As String) '原先只有輸入列號，現在要加上工作表的名字
    
21.     If Worksheets(sheetName).Cells(rowChoose, 5) > 10 Then Flag = 1        '只選取指定sheet的資料做篩選
    
22. End Sub
    
23. Sub CopyPasteVer2(rowCopy, rowPaste, copySheet As String, pasteSheet) 'copy the row rowcopy in sheet with name "2330"
    
24.     Dim myStr As String                                                             'and paste to the row rowpaste in the  sheet "pick"
    
25.     Sheets(copySheet).Select
    
26.     myStr = rowCopy & ":" & rowCopy
    
27.     Rows(myStr).Select
    
28.     Selection.Copy
    
29.     Sheets(pasteSheet).Select
    
30.     myStr = "A" & rowPaste
    
31.     Range(myStr).Select
    
32.     ActiveSheet.Paste
    
33. End Sub
    
34. Sub main3()
    
35.     Dim i As Integer
    
36.     Dim myRange As Range
    
37.     Dim myCell
    
38.     Dim mySheet As String
    
39.     mySheet = InputBox("input the sheet name you analyze") '選擇工作表
    
40.     Set myRange = Application.InputBox("Choose the days", Type:=8)
    
41.     '幫我選取我要篩選的範圍   ** 要選取整列  **
    
42.     Set myRange = myRange.SpecialCells(xlCellTypeConstants)   '選取整列有資料的範圍
    
43.     addsheetVer2 '執行上述新增工作表的程式，每次增加的不一樣，可以執行上面寫的好幾個副程式，可以讓每個程式分工合作，組合在一起
    
44.     myRow = 1
    
45.     For Each myCell In myRange '在我的myrange裡對每一個mycell，來做下面的事情
    
46.         i = myCell.Row
    
47.         Flag = 0 '不符合我的要求就跳到下一圈去看是否有符合
    
48.         ChooseVer2 i, mySheet '檢測第20行是否符合我設的條件
    
49.         If Flag = 1 Then
    
50.             CopyPasteVer2 i, myRow, mySheet, XpasteSheet '在指定的工作表作篩選後貼過去，符合設定條件，貼到新的工作表，因為不是只有第二十行，所以要寫迴圈
    
51.             myRow = myRow + 1
    
52.         End If
    
53.     Next
    
54. End Sub

複製代碼

---

作者: yagami12th    時間: 2012-2-27 14:18

回復 13# GBKEE

太感謝GBKEE大了:D ，但對於GOTO方法還沒用過，要查一下。

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)