0

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
nathan
  • 15
  • 3
  • If you can show the code/function that you already have, it will be much easier to help you resolve the problem... – Dave May 19 '18 at 01:29
  • Hi, I've added in the code if that helps. I basically want it to turn it into csv files after all the transformations are complete. – nathan May 19 '18 at 01:32
  • Have a look at the answer provided by Siddarth in this question: https://stackoverflow.com/questions/38928984/save-specific-single-sheet-as-csv – Dave May 19 '18 at 01:45
  • That should give you what you want – Dave May 19 '18 at 01:45
  • Thanks for the reply. If I understood it correctly, I think that it wouldn't work if I added another Data4? – nathan May 19 '18 at 01:51
  • Added an answer for you - should work for whatever worksheets you add. – Dave May 19 '18 at 02:06

1 Answers1

0

Given that you are passing the worksheet directly into the function, you can apply a small change to the Sub to do as you require, for however many and whichever worksheets you pass into it:

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

' Now make a copy of the Worksheet to a new workbook
w.Copy
' And save the newly created workbook with that sheet as a csv
ActiveWorkbook.SaveAs Filename:="C:\myPath\" & w.Name & ".csv", _
                  FileFormat:=xlCSV
End Sub
Dave
  • 4,328
  • 2
  • 24
  • 33