Have a look and see if this does what you need. Alternative methods always have advantages and disadvantages.
The original code using .value = .value
This will normally remove the leading zeroes and any number formatting (surprisingly the dates seem to be ok).
The issue is that you want to keep the leading zeroes but what do with the thousands delimiter, does that need to be removed ?
The paste I have used below will keep both. Let me know if that is going to be an issue.
The alternative is to hard code which column has the leading zeroes in it so we can treat it differently, in which case tell me which column and I will modify it.
VBA Code:
Sub Export_Sheets()
Dim wbk1 As Workbook, wbk2 As Workbook
Dim sh As Worksheet, rng As Range
Dim LastRow As Long
Dim fldrName As String
' Don't show confirmation window
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wbk1 = ThisWorkbook
fldrName = "\\SERVER ADDRESS"
For Each sh In wbk1.Sheets
If sh.Name = "Upload" Then
' Find last row non blank row (where the term blank includes formulas returning "")
LastRow = sh.Cells.Find("*", LookIn:=xlValues, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
Set rng = sh.Range("A1").CurrentRegion.Resize(LastRow)
Set wbk2 = Workbooks.Add
rng.Copy
wbk2.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
wbk2.SaveAs Filename:=fldrName & "/" & sh.Name & ".csv", FileFormat:=xlCSV, local:=True
wbk2.Close
End If
Next sh
' Allow confirmation windows to appear as normal
Application.DisplayAlerts = True
Application.ScreenUpdating = True
' XXX Not sure that is safe to assume that the main workbook has already been saved
'ActiveWorkbook.Saved = True
' XXX This is quite risky since you might have multiple workbooks open in this instance of excel
'Application.Quit
End Sub