1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
| Option Explicit
Sub recmoon()
Dim rng_in As Range
Dim rng_out As Range
Dim wksht As Worksheet
Dim i As Integer
Application.Visible = False
Application.Calculation = xlCalculationManual
For Each wksht In Worksheets
If wksht.Name <> "source" Then
With wksht
If .Columns(1).Find("*", , , , , xlPrevious).Row <> 1 Then
.Rows("2:" & .Columns(1).Find("*", , , , , xlPrevious).Row).Delete
End If
End With
End If
Next wksht
With Worksheets("source")
Set rng_in = .Range("A1")
For i = 1 To .Columns(1).Find("*", , , , , xlPrevious).Row - 1
With Worksheets(rng_in.Offset(i, 0).Value)
Set rng_out = .Columns(1).Find("*", , , , , xlPrevious).Offset(1, 0)
rng_in.Offset(i, 0).EntireRow.Copy
rng_out.PasteSpecial
End With
Next i
End With
Application.Calculation = xlCalculationAutomatic
Application.Visible = True
End Sub |
Partager