Hi,
I have a bit of a mammoth of a database I'm trying to extract data from and I'm a complete VBA novice. Basically across the top I have titles of the data source (merged across 10 columns) and then underneath that are the year of the data going across - 2000, 2001, 2002 etc etc. In each of these columns in the 10 rows down, there may or may not be a number value in the cells, depending on if i have data for these years. This repeats for 5 different data sources (all with 10 columns with the years 2000-2010).

This is then repeated in a different worksheet for each country (around 30 countries)

I'm trying to consolidate the data that I have (so not the years I'm missing) into one sheet but since there is more than the number of columns excel will allow me if I do a straight copy and paste job, and since on the summary sheet I only want to see the years I have data for (and more data may be entered later on), I thought a macro would be a good idea. However I can't figure it out. Any advice would be much appreciated...!

So, how do I write the code to basically say 'if there is a number in any cell in d3:d15 in 'worksheet 1', then copy and paste d2:d15 (d2 so it includes the year of the data), and paste into 'worksheet 2' at d4. I want to be able to then loop or repeat this so that it then moves onto e3:e15 and pastes in worksheet 2 in e4 or in the next available column so there aren't any data gaps (if that's possible). Its the 'copy only if there's data' that I can't figure out and the 'paste in the next column'

Does that make any sense? I've spent the last 5 hours trying to teach myself this and can't figure it out. I could figure out how to copy the whole lot across and then delete the entire columns if it didn't have data in the range and that worked really well for a bit (and I was supper excited!) but it seems to not like it once there is data in there as it says that it can't use the command to delete the columns when there are overlapping cells (because once i delete and shift to the left, the next data selection is overlapped by the first).

That probably doesn't make sense but I'm so confused I'm not really sure how to explain myself - hopefully someone can make something out of this ramble!
Thanks!