@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.
