win-sun
Thành viên hoạt động
- Tham gia
- 19/1/09
- Bài viết
- 151
- Được thích
- 15
- Trong một lần em sưu tầm được code này, em thấy hay và muốn phát triển thêm tí nữa để sử dụng.
- Điều em mong muốn: sau khi chạy code trong file tổng hợp, dử liệu sẽ được chép nối tiếp (theo thứ tự từ trên xuống) vào file tổng hợp, và sau khi đã coppy dử liệu từ các file trong cùng folder đó nó sẽ quay lại và xóa hết các file vừa coppy đó luôn. xin cảm ơn
Sub Tonghop()
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Set WF = WorksheetFunction
FolderName = ActiveWorkbook.Path
Set SourceWb = ThisWorkbook
wName = ActiveWorkbook.Name
wbName = Dir(FolderName & "\" & "*.xls")
With Sheets("Sheet1")
.Range("A9:H10000").ClearContents
.Range("H8") = 0
End With
'eRow = 9
While wbName <> ""
If wbName <> wName Then
If bWorkbookIsOpen(wbName) Then
Windows(wbName).Activate
Else
Workbooks.Open filename:=FolderName & "\" & wbName
End If
Set TgtWb = ActiveWorkbook
shName = "sheet1"
eRow = SourceWb.Sheets("Sheet1").Range("H65000").End(xlUp).Row + 1
If WksExists(shName) = True Then
Sheets(shName).Select
'Tim dong cuoi cua sh can copy'
Set myRng = Range("A9:F10000")
If WF.CountA(myRng) > 0 Then
With myRng
LastRow = .Find(What:="*", After:=[A9], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'LastColumn = .Find(What:="*", After:=[A9], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column'
End With
End If
Set myRng = Nothing
endR = LastRow
'Phan nay cat bot dong trong cua Sh paste bang cach sort'
With SourceWb.Sheets("Sheet1")
.Range("A" & eRow & ":F" & eRow + endR - 9).Value = Range("A9:F" & endR).Value
.Range("H" & eRow & ":H" & eRow + endR - 9).FormulaR1C1 = "=IF(COUNTA(RC1:RC6)=0,""x"",MAX(R8C8:R[-1]C8)+1)"
.Range("H" & eRow & ":H" & eRow + endR - 9).Value = .Range("H" & eRow & ":H" & eRow + endR - 9).Value
Set myRng = .Range("A" & eRow & ":H" & eRow + endR - 9)
With myRng 'sort theo cot H'
.Sort Key1:=.Range("H" & eRow), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
Set myRng = .Range("H" & eRow & ":H" & eRow + endR - 9)
solan = WF.CountIf(myRng, "x")
If solan > 0 Then
SourceWb.Sheets("Sheet1").Rows(eRow + endR - 9 - solan + 1 & ":" & eRow + endR - 9).Delete Shift:=xlDown
End If
End With
End If
TgtWb.Close
End If
wbName = Dir
Wend
With Sheets("Sheet1")
.Columns("H:H").ClearContents
End With
Set myRng = Nothing
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function
Function bWorkbookIsOpen(rsWbkName As String) As Boolean
On Error Resume Next
bWorkbookIsOpen = CBool(Len(Workbooks(rsWbkName).Name) > 0)
End Function
- Điều em mong muốn: sau khi chạy code trong file tổng hợp, dử liệu sẽ được chép nối tiếp (theo thứ tự từ trên xuống) vào file tổng hợp, và sau khi đã coppy dử liệu từ các file trong cùng folder đó nó sẽ quay lại và xóa hết các file vừa coppy đó luôn. xin cảm ơn
Sub Tonghop()
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Set WF = WorksheetFunction
FolderName = ActiveWorkbook.Path
Set SourceWb = ThisWorkbook
wName = ActiveWorkbook.Name
wbName = Dir(FolderName & "\" & "*.xls")
With Sheets("Sheet1")
.Range("A9:H10000").ClearContents
.Range("H8") = 0
End With
'eRow = 9
While wbName <> ""
If wbName <> wName Then
If bWorkbookIsOpen(wbName) Then
Windows(wbName).Activate
Else
Workbooks.Open filename:=FolderName & "\" & wbName
End If
Set TgtWb = ActiveWorkbook
shName = "sheet1"
eRow = SourceWb.Sheets("Sheet1").Range("H65000").End(xlUp).Row + 1
If WksExists(shName) = True Then
Sheets(shName).Select
'Tim dong cuoi cua sh can copy'
Set myRng = Range("A9:F10000")
If WF.CountA(myRng) > 0 Then
With myRng
LastRow = .Find(What:="*", After:=[A9], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'LastColumn = .Find(What:="*", After:=[A9], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column'
End With
End If
Set myRng = Nothing
endR = LastRow
'Phan nay cat bot dong trong cua Sh paste bang cach sort'
With SourceWb.Sheets("Sheet1")
.Range("A" & eRow & ":F" & eRow + endR - 9).Value = Range("A9:F" & endR).Value
.Range("H" & eRow & ":H" & eRow + endR - 9).FormulaR1C1 = "=IF(COUNTA(RC1:RC6)=0,""x"",MAX(R8C8:R[-1]C8)+1)"
.Range("H" & eRow & ":H" & eRow + endR - 9).Value = .Range("H" & eRow & ":H" & eRow + endR - 9).Value
Set myRng = .Range("A" & eRow & ":H" & eRow + endR - 9)
With myRng 'sort theo cot H'
.Sort Key1:=.Range("H" & eRow), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
Set myRng = .Range("H" & eRow & ":H" & eRow + endR - 9)
solan = WF.CountIf(myRng, "x")
If solan > 0 Then
SourceWb.Sheets("Sheet1").Rows(eRow + endR - 9 - solan + 1 & ":" & eRow + endR - 9).Delete Shift:=xlDown
End If
End With
End If
TgtWb.Close
End If
wbName = Dir
Wend
With Sheets("Sheet1")
.Columns("H:H").ClearContents
End With
Set myRng = Nothing
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function
Function bWorkbookIsOpen(rsWbkName As String) As Boolean
On Error Resume Next
bWorkbookIsOpen = CBool(Len(Workbooks(rsWbkName).Name) > 0)
End Function