need a macro to add columns and copy data

Hi guys,

I'm trying to record a macro which would add 2 columns, first column will be the file name of the spreadsheet, and the second column will copy the data from a column in the sheet and change the number format from 1 to 0001.
I tried a few things but haven't had any success yet.. i appreciate your help.
I'm attaching the file. Sheet 1 has original data, and sheet 2 is how I want it to look like.

thanks in advance.