HSV
Inventaris
- Lid geworden
- 18 jul 2008
- Berichten
- 20.304
- Office versie
- Office 365
Zoiets?
Code:
Sub ExportLabels2017() 'hsv
Dim Sh As Worksheet, sn, sp, st, j As Long, c00 As String
Application.ScreenUpdating = False
With GetObject(ThisWorkbook.Path & "\ImportLabels.xls")
.Windows(1).Activate
With .Sheets("2017Labels")
.Range("A2:A2500").Clear
.Range("F2:F2500").Clear
.Range("G2:G2500").Clear
.Range("H2:H2500").Clear
For Each Sh In ThisWorkbook.Sheets
If Len(Sh.Name) = 9 Then
sn = Sh.Range("B22:H" & Application.Max(22, Sh.Cells(123, 2).End(xlUp).Row))
sp = Sh.Range("B236:U" & Application.Max(236, Sh.Cells(123, 2).End(xlUp).Row + 214))
For j = 1 To UBound(sn)
c00 = c00 & Replace(String(sn(j, 7), " "), " ", " " & j)
Next
If c00 <> "" Then
st = Application.Transpose(Split(Trim(c00)))
.Cells(Application.Max(2, .Cells(Rows.Count, 1).End(xlUp).Offset(1).Row), 1).Resize(UBound(st)) = Application.Index(sn, st, 1)
.Cells(Application.Max(2, .Cells(Rows.Count, 6).End(xlUp).Offset(1).Row), 6).Resize(UBound(st)) = Application.Index(sn, st, 7)
.Cells(Application.Max(2, .Cells(Rows.Count, 7).End(xlUp).Offset(1).Row), 7).Resize(UBound(st)) = Sh.Name
.Cells(Application.Max(2, .Cells(Rows.Count, 8).End(xlUp).Offset(1).Row), 8).Resize(UBound(st)) = Application.Index(sp, st, 20)
End If
End If
c00 = ""
Next Sh
End With
End With
End Sub
Laatst bewerkt: