I've got 4 separate worksheets - one that is titled main, one that is titled Data 1, one that is titled Data 2 and one that is titled Data 3.
I have a button on the main sheet that when I click transforms all three of the Data Sheets using the same function but what I'm stuck on is that I also want it to transform the 3 data sheets into 3 separate CSV files with the names "output_data1.csv", "output_data2.csv" and "output_data3.csv" but also for it to be flexible enough to work on another data sheet 4 if I add it in later.
Is there any way that I can do this? The main thing that I'm stuck on is being able to rename it as well but keep the original excel file unchanged.
Sub DumpOutput()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Main" Then
ProcessData ws
End If
Next ws
End Sub
Sub ProcessData(ByRef w As Worksheet)
Dim N As Long, wf As WorksheetFunction, M As Long
Dim i As Long, J As Long
Dim rng As Range
Dim Temp
Dim nams As Variant
Dim F
Dim Dex As Integer
N = Columns.Count
M = Rows.Count
With w
Set wf = Application.WorksheetFunction
Application.ScreenUpdating = False
For i = N To 1 Step -1
If wf.CountBlank(.Columns(i)) <> M Then Exit For
Next i
For J = i To 1 Step -1
If wf.CountBlank(.Columns(J)) = M Then
.Cells(1, J).EntireColumn.Delete
End If
Next J
For J = M To 1 Step -1
If wf.CountBlank(.Rows(J)) <> N Then Exit For
Next J
For i = J To 1 Step -1
If wf.CountBlank(.Rows(i)) = N Then
.Cells(1, i).EntireRow.Delete
End If
Next i
Application.ScreenUpdating = True
nams = Array("NAME", "TICKER", "PRICE", "CURRENCY", "ISIN", "TYPE")
Set rng = .Range("A1").CurrentRegion
For i = 1 To rng.Columns.Count
For J = i To rng.Columns.Count
For F = 0 To UBound(nams)
If nams(F) = rng(J) Then Dex = F: Exit For
Next F
If F < i Then
Temp = rng.Columns(i).Value
rng(i).Resize(rng.Rows.Count) = rng.Columns(J).Value
rng(J).Resize(rng.Rows.Count) = Temp
End If
Next J
Next i
.Range("f1:f13") = Application.Transpose(Array("TYPE", "Stock", "Stock", "Stock", "Index", "Stock", "Stock", "Stock", "Index", "Stock", "Stock", "Stock", "Index"))
w.Cells.EntireColumn.AutoFit
Debug.Print .Name
End With
End Sub