چنانچه قصد تجمیع کلیه شیت ها را در شیت کل دارید
فایل پیوست را بررسی بفرمایید.
کد PHP:
Private Sub Worksheet_Activate()
Application.EnableEvents = False
Application.ScreenUpdating = False
Z0 = Cells(Rows.Count, "B").End(xlUp).Row
If Z0 = 1 Then Z0 = 2
Range("A2:C" & Z0).ClearContents
For Each Sheet In Worksheets
If Sheet.Name <> "kol" Then
Sheet.Select
Z1 = Sheet.Cells(Sheet.Rows.Count, "B").End(xlUp).Row
Sheet.Range("B2:C" & Z1).Copy
SH = Sheet.Name
Sheets("KOL").Select
Z2 = Cells(Rows.Count, "B").End(xlUp).Row + 1
Range("B" & Z2).Select
ActiveSheet.Paste
Z3 = Cells(Rows.Count, "B").End(xlUp).Row
For I = Z2 To Z3
Range("A" & I) = SH
Next
End If
Next
Range("A1").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
علاقه مندی ها (Bookmarks)