Macro to copy E12, F12, G12 etc to next empty cell from I36 down eg I44,I45 etc

[rain4u]

Hi I have this weekly information that I would like to store VIA macro.
The information will always be in same row (always the same range) from E12 to K12 (so that is horizontally) Monday to Friday.

Now in the end of the week I would like to copy this information from each of these cells to next available cell in column i row 36 downwards which is vertically. In my attached example the next available empty cell in row i would be i44


So the macro should turn this
E12 ----F12----G12----H12----I12----J12----K12
Mon ----Tue----Wed----Thur----Fri----Sat----Sun
---1------- 4------- 2------- 2------- 3------- 0------- 0


Into this
------------------Col i-------
Row 44---------1-------
Row 46---------4
Row 47---------2
Row 48---------2
Row 49---------3
Row 50---------0
Row 51---------0

So the week after if I run macro again it would take the new information from E12 to K12 and store it from row 52 onwards.


Can this be done?


Any help is appreciated.

[MarvinP]

Hey rain4u, I'm in Seattle and sick of the rain, but here is your answer.

Find the attached with this macro. It is mostly Macro Record with a few loops.

Please Login or Register  to view this content.

[rain4u]

It does something but not what it should. First I didn't even realize what it was doing. So In disabled the screen update.It looks like it does copy the information over from E12 to K12. But when it should paste or move the information to the right place it gets it wrong. All the information is being pasted to row 303 downwards. I guess it takes its start up line according to column C. And its messed up as it also copies over information from column C to col W. Very randomly the first row that is being copied is row 32. rest of it is hard to figure out.

Any idea what needs to be tweaked?


Edit:

Oh sorry. i just copied and pasted the macro from the forum. Didn't use the xls attached. The information in column A, B, C should always be there as in original spreadsheet. If thats the case then the problem is same. It also should not copy info to other cells. What can we try to change?



PS! It was nice and sunny here today in Bristol!

Cheers
Rain

[MarvinP]

I deleted all the dates down column C.
See my sheet.

The second question is I did a Paste Transpose using macro recorder in 2010. I'm not sure the same VBA is in 2003. Record a macro and Copy a Column of a single date and paste it TRANSPOSE to where you want it to go. See if the 2003 VBA is the same as the 2010 vba. If not then change that Paste line in my code.

[rain4u]

By the looks of it transpose does exist in 2003 VBA. Unfortunately I need to keep the information in column "C". Plus even without that it still copies random information to range D37 to W37.


Do you have any other ideas or does someone else have alternative solution?



Cheers
Rain

[MarvinP]

Hi Rain,

Plus even without that it still copies random information to range D37 to W37.

Doesn't help me much to help solving your problem.

Part of your job is to figure out WTF random stuff is and see why it is there.

Do you have formulas in the columns of data that are being copied?
After the copy to the rows do these formulas then look like "random information"

If you were to copy using values only does the "random stuff" come along?

If you need all those dates in Column C then I guess you have to do this problem a different way or perhaps do a lookup to see where to paste the data. Or perhaps you copy it to the bottom of the dates, delete duplicates and then sort by dates.

I think all the above will work but it is still your problem.

[rain4u]

Thank you Marvin for your time.
I have an idea how to approach this problem from a slightly different angle that will probably give a better chance to have a solution. I'm updating the xls and I will post it here later when I'm finished.
Thank you for your contribution so far. I have used your macros before so I know what you are capable with Excel. I wish I would know this stuff myself so I wouldn't have to rely on others always helping me. I'm definitely getting better. It just takes like forever to learn all this stuff.


Cheers
Rain

[watersev]

hi, rain4u, please run code "test"

PS. Never do such things as drawing borders till the end of sheet rows. You drastically increase the file size. Your file size is 2.37 MB. Without that formating the file size is 356 KB.

[rain4u]

Hi
Thank you for the tip about borders. Bit common sense I suppose

I approached the the whole thing wrong way. The objective is to store information for statistics and charts to identify some key problems. My silliness was to thinking how to store information in real-time day to day basis. And as I don't get some of the information the same day (I have to go back and have to do some of the figures the next day) that idea started to look really quite confusing and nightmarish. Now I realized there is no hurry. I don't understand where I even took the idea to do data storing part day by day. I can complete my data throughout the whole week and review it on Mondays and then store the data. I still need help but I think it will be easier to help me as it will be a lot more straight forward.

My question is can the following be achieved
Is it possible to transfer vertically stored data into horizontal layout using the dates and categories as a reference point.

Source information has dates from E6 to I6 (horizontal) and categories in D7 to D27 (vertical)

Destination layout has it the opposite way. Dates are in C37 to C226 (vertical) and categories are from D36 to X36

So lets say i will complete my data for the whole week and the run macro to store it. I have attached the updated xls with sheet "before" and "after"


PS! I didn't even know about paste-transpose. I wish I known about it before.

[watersev]

hi, rain4u, please check attachment, run code "test"

[rain4u]

Thank you. It works spot on! I will now close the thread.



Cheers
Rain