EXCEL- VBA 移動資料

1 工具 / 巨集 / VB編輯
2 點選左側工作表 / 輸入下列內容
Private Sub Worksheet_Activate()

Dim r, i, sr, sc


Range("c1").Select

i = 1
While (ActiveCell.Value <> Empty)
If ActiveCell.Value = "y" Then
sr = i & ":" & i
Rows(sr).Select
Selection.Cut
Sheets("y").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select

Sheets("all").Select
Rows(sr).Select
Selection.Delete Shift:=xlUp
sc = "c" & (i)

Range(sc).Select
Else
sc = "c" & (i + 1)

Range(sc).Select
i = i + 1
End If

Wend



End Sub
註 : 會自動將欄C內儲存格的值為Y移至


工作表Y

Private Sub Worksheet_Activate()

Sheets("y").Select
Range("A1").Select
Selection.End(xlDown).Select
x = ActiveCell.Row
If x = 65536 Then

Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
End Sub

註:此為自動偵測資料內容