aks_sv
Junior Member | Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Может кто подскажет, что сдесь лишнее?
Sub Конвертировать()
'
Application.ScreenUpdating = False
ActiveWindow.TabRatio = 0.335
Sheets("TDSheet").Name = "Ведомость"
Sheets.Add
Sheets("Лист1").Move After:=Sheets(2)
Sheets("Лист1").Name = "Остаток"
Worksheets("Остаток").Range("A1").Value = "Наименование"
Worksheets("Остаток").Range("B1").Value = "Номенклатурный номер"
Worksheets("Остаток").Range("C1").Value = "Остаток кол-во"
Worksheets("Остаток").Range("D1").Value = "Остаток цена"
Worksheets("Остаток").Range("E1").Value = "Приход количество"
Worksheets("Остаток").Range("F1").Value = "Приход цена"
Sheets.Add
Sheets("Ведомость").Select
ActiveWindow.DisplayOutline = False
Rows("1:11").Select
Selection.Delete Shift:=xlUp
Range("A:A,D:D,F:F,G:G,H:H").Select
Range("G1").Activate
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Dim r As Long, LastRow As Long
LastRow = Worksheets("Ведомость").Range("A65536").End(xlUp).Row
For r = 1 To LastRow
If Cells(r, 1).Font.Bold = True Then
Rows(r + 1).Delete Shift:=xlUp
End If
If Cells(r, 1).Font.Bold = True Then
Rows(r).Delete Shift:=xlUp
End If
Next r
Dim pSource As Worksheet, pCell As Range
Dim pDest As Worksheet
Dim i As Long, pos As Long
Set pSource = ActiveSheet
Set pDest = Worksheets("Остаток")
pos = 1&
For i = 1& To pSource.UsedRange.Rows.Count - 3& Step 4&
pos = pos + 1&
Set pCell = pSource.Cells(i, 1&)
pDest.Cells(pos, 1&).Value = pCell.Value
pDest.Cells(pos, 2&).Value = pCell.Offset(2&, 0&).Value
pDest.Cells(pos, 3&).Value = pCell.Offset(1&, 1&).Value
pDest.Cells(pos, 4&).Value = pCell.Offset(0&, 1&).Value
pDest.Cells(pos, 5&).Value = pCell.Offset(1&, 2&).Value
pDest.Cells(pos, 6&).Value = pCell.Offset(0&, 2&).Value
pDest.Cells(pos, 7&).Value = pCell.Offset(1&, 3&).Value
pDest.Cells(pos, 8&).Value = pCell.Offset(0&, 3&).Value
Next i
Worksheets("Остаток").Activate
Columns("A:A").ColumnWidth = 40
Columns("B:B").ColumnWidth = 15.83
Columns("C:C").ColumnWidth = 11
Columns("D:D").ColumnWidth = 11
Columns("E:E").ColumnWidth = 11
Columns("F:F").ColumnWidth = 11
Columns("B:B").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
Columns("C:C").Select
Selection.NumberFormat = "#,##0.000"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
End With
Columns("D:D").Select
Selection.NumberFormat = "#,##0.00"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
End With
Columns("E:E").Select
Selection.NumberFormat = "0.000"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
End With
Columns("F:F").Select
Selection.NumberFormat = "#,##0.00"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
End With
Rows("1:1").RowHeight = 29.25
Range("A1:F1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With
Dim FinalRow As Long
FinalRow = Worksheets("Остаток").Range("D65536").End(xlUp).Row + 1
Cells(FinalRow, 4).FormulaR1C1 = "=SUM(R2C4:R[-1]C)"
Cells(FinalRow, 6).FormulaR1C1 = "=SUM(R2C6:R[-2]C)"
Cells(FinalRow, 4).Font.Bold = True
Cells(FinalRow, 6).Font.Bold = True
Range("A1").Select
End Sub
|
Всего записей: 109 | Зарегистр. 18-12-2006 | Отправлено: 15:55 21-04-2007 | Исправлено: aks_sv, 16:00 21-04-2007 |
|
