It's not the methode to save the created workbook but it's about the trick
to create a workbook* with a specific name* *without* saving it.
You'd like to see the code, it has nothing to do with this isue because this
routine creates the export before creating the workbook it self.
But anyway:
Public Sub MaakExport()
Dim arExport(15, 651) As Variant
Dim arColumnheads
Dim wbExport As Workbook
msg = "De export voor de ketenplanning wordt gemaakt in een nieuwe werkmap
en bevat" + vbCr + _
"enkel de voertuigen die opgenomen zijn in de ketenplanning." + vbCr
+ vbCr + _
"De werkmap kunt u desgewenst opslaan voor later gebruik." + vbCr +
vbCr + _
"Wilt u doorgaan?"
If MsgBox(msg, vbYesNo + vbQuestion, "KetenPlanning") <> vbYes Then Exit
Sub
Application.ScreenUpdating = False
shVoertuigenlijst.Activate
s = 0
For r = 2 To 651
b = Val(Left(Cells(r, [rngStatusvoortgang].Column), 2))
If b < 80 And b > 0 Then
If Cells(r, [rngKeten].Column).Value <> "nvt" Then
With shVoertuigenlijst
arExport(0, s) = .Cells(r, [rngKOMnr].Column).Value
arExport(1, s) = .Cells(r, [rngVoertuigen].Column).Value
arExport(2, s) = .Cells(r, [rngKlant].Column).Value
' arExport(3, s) = .Cells(r, [].Column).value
arExport(4, s) = .Cells(r, [rngIKVT].Column).Value
arExport(5, s) = .Cells(r, [rngVTstart].Column).Value
arExport(6, s) = .Cells(r, [rngVTeind].Column).Value
arExport(7, s) = .Cells(r, [rngIKHI].Column).Value
arExport(8, s) = .Cells(r, [rngHIstart].Column).Value
arExport(9, s) = .Cells(r, [rngHIeind].Column).Value
' arExport(10, s) = .Cells(r, [].Column).value
arExport(11, s) = .Cells(r, [rngPlanbordnummers].Column).Value
arExport(12, s) = .Cells(r, [rngStartdatum].Column).Value
arExport(13, s) = .Cells(r, [rngEinddatum].Column).Value
arExport(14, s) = .Cells(r, [rngPVEdatum].Column).Value
arExport(15, s) = .Cells(r, [rngOpmerkingen].Column).Value
End With
s = s + 1
End If
End If
Next
Set wbExport = Application.Workbooks.Add
With wbExport.Worksheets(1).Range("A1:P1")
.BorderAround ColorIndex:=1, Weight:=xlThin
.Borders(xlInsideVertical).ColorIndex = 1
.Borders(xlInsideVertical).Weight = xlThin
.Value = Array("KOMnr", "Voertuig", "Klant naam", "", "VT IKnr", "VT
start", "VT eind", _
"HI IKnr", "HI start", "HI eind", "", "Pb#", "HNC start", "HNC
eind", _
"PVE datum", "Opmerkingen")
End With
With wbExport.Worksheets(1).Range("A2:P652")
.ClearContents
.Value = Application.WorksheetFunction.Transpose(arExport)
.EntireColumn.AutoFit
End With
Application.ScreenUpdating = True
wbExport.Worksheets(1).Activate
Set wbExport = Nothing
End Sub
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to [email protected]
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
