bftbeckett
New Member
- Joined
- Apr 20, 2022
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
I am attempting to create an array to create a mapping file but I keep getting a Subscript out of bounds error on the tempArray function. I can't figure out what I need to do.
Sub SplitEachWorksheet()
Dim FPath, FileExtStr, DateString, xFile As String
Dim FileFormatNum As Long
Dim itemCount As Integer
Dim tempArray() As Variant
Dim result As Variant
Dim Filename As Variant
FPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets
ws.Copy
Application.ActiveWorkbook.SaveAs Filename:=FPath & "/" & ws.Name & " " & Split((DateDiff("s", "01/01/1970", Date) + Timer) * 1000, ".")(0) & Int((99999 - 1 + 1) * Rnd + 1) & ".csv"
Next
itemCount = 0
result = Filename
tempArray(Filename) = result
result = ""
itemCount = itemCount + 1
Application.ActiveWorkbook.Close False
generateMappingFile (tempArray)
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Sub SplitEachWorksheet()
Dim FPath, FileExtStr, DateString, xFile As String
Dim FileFormatNum As Long
Dim itemCount As Integer
Dim tempArray() As Variant
Dim result As Variant
Dim Filename As Variant
FPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets
ws.Copy
Application.ActiveWorkbook.SaveAs Filename:=FPath & "/" & ws.Name & " " & Split((DateDiff("s", "01/01/1970", Date) + Timer) * 1000, ".")(0) & Int((99999 - 1 + 1) * Rnd + 1) & ".csv"
Next
itemCount = 0
result = Filename
tempArray(Filename) = result
result = ""
itemCount = itemCount + 1
Application.ActiveWorkbook.Close False
generateMappingFile (tempArray)
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub