Forum Discussion
Macros and VBA to export data from excel file in specific order into a new excel spreadsheet
Greetings, I need to rearrange my data to link it to my PowerBI Dashboard. I was wondering if you could advise me how to do it? I have 75 spreadsheets with 177 rows each. Is there any way to automa...
Thank you, it doesn't show month and year. The output shows as below:
A 10 0
A 60 0
A 101 0
B 20 0
B 70 0
B 201 0
C 30 0
C 80 0
C 202 0
D 40 0
D 90 0
D 302 0
A 10 0
A 60 0
A 101 0
B 20 0
B 70 0
B 201 0
C 30 0
C 80 0
C 202 0
D 40 0
D 90 0
D 302 0
You can add the following line of code to include the month and year in the output:
mon = Format(rng.Cells(1, c).Value, "mmmm")
yr = Format(rng.Cells(1, c).Value, "yyyy")This will format the date in the cell to show the month and year in the output.
You can replace the Case statement with the above code to get the desired output.
- Thank you so much for your help. Unfortunately, I couldn't make it work so I found another solution which was much easier, so I created custom function in Power Query.