Copy data in one format from Sheet1 and paste to another sheet in another format.

Hi Everyone,

I just found this forum today and it seems to be exactly what I was looking for. I am having a problem with reformatting the data I have pulled from a bloomberg terminal.

Problem 1) The date in columnA for every company listed comes up with an error. I would like to replace it with the proper date. Unfortunately, the dates are not all the same (i.e. I cannot "replace all" with the date 31/12/2011) and should be a month earlier than the date in columnB.

Problem 2) I would like to change the format completely as outlined in the attached workbook. Because the data for every company does not span the period 31/12/2011 - 01/01/2002, some companies have data that start in columnA as a different date such as 01/01/2005, etc. I want to pull this data the sheet "Target Format" so that it is easier to manipulate - if there is no data for a company for a certain date, I want a value of 0 to be returned.


I have to do this with multiple sheets so ideally a macro would be ideal. All source data sheets have the same format as in the example. I have no idea how I would go about copying the data to thesought format. Any ideas or advice?


Data Format Change.xlsx

Try it. But note the rows 193-194, 352-353, 411-412, 558-559, 905-906 on the 'Current format' tab in your file.

Anyone have any ideas???

Try it. Go to the worksheet with the data and run the macro (press Alt+F8, select 'ertert33', press 'Run').

Originally Posted by nilem

Try it. Go to the worksheet with the data and run the macro (press Alt+F8, select 'ertert33', press 'Run').

nilem, thank you so much. you are a lifesaver!