Modified the code to try to get it to loop, it almost works.
For some reason, it only outputs anywhere from ~125 - 136 CSV files (it varies every time I run it), and only seems to work for two worksheets (there's about 30 worksheets).
Should be outputting many more CSV files (~1000).
Any idea on what's wrong in my code?
Link to workbook I'm working with
Option Explicit
Public counter As Integer
Sub Create_CSVs_AllSheets()
Dim sht 'just a tmp var
counter = 1 'this counter will provide the unique number for our 1.csv, 2.csv.... 999.csv, etc
appTGGL bTGGL:=False
For Each sht In Worksheets ' for each sheet inside the worksheets of the workbook
If sht.Name <> "AA" And sht.Name <> "Word Frequency" Then
'IF sht.name is different to AA AND sht.name is diffent to WordFrecuency THEN
'TIP:
'If Not sht.Name = noSht01 And Not sht.Name = noSht02 Then 'This equal
'IF (NOT => negate the sentence) sht.name is NOT equal to noSht01 AND
' sht.name is NOT equal to noSht02 THEN
sht.Activate 'go to that Sheet!
Create_CSVs_v3 (counter) 'run the code, and pass the counter variable (for naming the .csv's)
End If '
Next sht 'next one please!
appTGGL
End Sub
Sub Create_CSVs_v3(counter As Integer)
Dim ws As Worksheet, i As Integer, j As Integer, k As Integer, sHead As String, sText As String
Set ws = ActiveSheet 'the sheet with the data, _
'and we take the name of that sheet to do the job
For j = 5 To ws.Cells(1, Columns.Count).End(xlToLeft).Column
If ws.Cells(1, j) <> "" And ws.Cells(2, j) <> "" Then
sHead = ws.Cells(1, j)
sText = ws.Cells(2, j)
If ws.Cells(rows.Count, j).End(xlUp).Row > 2 Then
For i = 3 To ws.Cells(rows.Count, j).End(xlUp).Row 'i=3 because above we defined that_
'sText = ws.Cells(2, j) above_
'Note the "2" above and the sText below
sText = sText & Chr(10) & ws.Cells(i, j)
Next i
End If
Workbooks.Add
ActiveSheet.Range("A1") = sHead
ActiveSheet.Range("B1") = Chr(10) & sText 'Modified above line to start with "Return" character (Chr(10))
'instead of enclosing with quotation marks (Chr(34))
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & counter & ".csv", _
FileFormat:=xlCSV, CreateBackup:=False 'counter variable will provide unique number to each .csv
ActiveWorkbook.Close SaveChanges:=True
counter = counter + 1 'increment counter by 1, to make sure every .csv has a unique number
End If
Next j
Set ws = Nothing
End Sub
Public Sub appTGGL(Optional bTGGL As Boolean = True)
Debug.Print Timer
Application.ScreenUpdating = bTGGL
Application.EnableEvents = bTGGL
Application.DisplayAlerts = bTGGL
Application.Calculation = IIf(bTGGL, xlCalculationAutomatic, xlCalculationManual)
End Sub
Bookmarks