Macros and VBA to export data from excel file in specific order into a new excel spreadsheet

shibshib 

Here some information on how to export data from excel file in a specific order into a new excel spreadsheet using Macros and VBA.

Sub ExportData()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim rng As Range
    Dim r As Long
    Dim c As Long
    Dim cat As String
    Dim bud As Double
    Dim mon As String
    Dim yr As Long
    
    'Change this to your source workbook name
    Set wb = Workbooks("Source.xlsx")
    
    'Change this to your destination workbook name
    Workbooks.Add.SaveAs "Destination.xlsx"
    
    'Loop through all worksheets in source workbook
    For Each ws In wb.Worksheets
    
        'Change this to your data range address
        Set rng = ws.Range("A1:D6")
        
        'Loop through all rows and columns in data range
        For r = 3 To rng.Rows.Count
            For c = 2 To rng.Columns.Count
            
                'Get category, budget, month and year values from each cell
                cat = rng.Cells(r, 1).Value
                bud = rng.Cells(r, c).Value
                
                Select Case Left(rng.Cells(1, c).Value, 3)
                    Case "Dec"
                        mon = "December"
                        yr = Right(rng.Cells(1, c).Value, 4)
                    Case "Feb"
                        mon = "February"
                        yr = Right(rng.Cells(1, c).Value, 4)
                    Case "Mar"
                        mon = "March"
                        yr = Right(rng.Cells(1, c).Value, 4)
                    'Add more cases for other months if needed
                    
                End Select
                
                'Write the values to destination workbook in required format
                
                With Workbooks("Destination.xlsx").Worksheets(1)
                
                    .Cells(.Rows.Count, 1).End(xlUp).Offset(1) = cat
                    
                    .Cells(.Rows.Count, 2).End(xlUp).Offset(1) = bud
                    
                    .Cells(.Rows.Count, 3).End(xlUp).Offset(1) = mon
                    
                    .Cells(.Rows.Count, 4).End(xlUp).Offset(1) = yr
                    
                End With
                
            Next c
            
        Next r
        
        'Save and close destination workbook
        
        Workbooks("Destination.xlsx").Close SaveChanges:=True
    Next ws
   
End Sub

untested, without guarantee.

This code will loop through all the worksheets in your source workbook and extract the category, budget, month and year values from each cell.

Then it will write them to a new workbook in the required format.

You can change the workbook names, data range address and month cases as per your needs.

I hope this helps you with your task.