Transpose Data from Sheet1 to Sheet2

[rizmomin]

Hello:

Please refer TO ATTACHED EXCEL FILE.
(Earlier i had different requirement)

Sheet1 shows Clockin_ClockOut info for employees.

I need these data to be transpose in format shown in Sheet2.

VB macro or formula would work for me.

Please let me know if any questions.

Thank you

Riz Momin

[dilipandey]

Hi Rizmomin,

See the attached file and click on the blue button where I used below macro code:-

Book3.xlsm

Please Login or Register  to view this content.

Regards,
DILIPandey
<click on below * if this helps>

[AB33]

Another option

[rizmomin]

Hello DilipP & AB33:

Both solution will work for me.
Thank you so much to both of you for your great help.

Riz Momin

[rizmomin]

Hello DilipP :

Both solution will work for me.

If my data in Sheet1 is moved to O47 (Current cell referance is C1), what modification would i need to do to code.

Please let me know if any questions.

Thank you

Riz Momin
Thank you so much to both of you for your great help.

Riz Momin

[rizmomin]

Hi DilipP :

I figured it out, thanks a lot.
Change C2 to the position of the cell.

Riz Momin

[rizmomin]

Hi DilipP :

I figured it out, thanks a lot.
Change C2 to the position of the cell.

Riz Momin

[dilipandey]

You are welcome momin.... cheers


Regards,
DILIPandey
<click on below * if this helps>

[rizmomin]

Hi AB33:

I like your code.
Couple of questions, i have for your code.
If my data in Sheet1 is moved to O47 (Current cell referance is C1), what modification would i need to do to code.
Your code is limited to 7 rows, instead i want the code to work for all clockin-Clockout.
Some days i will have 6 employees and some days it could be 13.
So basically need to check data until employee filed is blank.

Also i need the value pasted since the source has formulas and data at source is calculated.

Please help and let me know if any questions.

Thank you

Riz

[AB33]

Riz,
Okay!
I am off site now and will back in few hours.

[rizmomin]

Hi AB33:

Thank you..

please refer to attached sheet.

Let ME KNOW IF ANY QUESTIONS.

Riz Momin

[jindon]

Try this.
Change "Sheet1" and "C1" to your reference.

Please Login or Register  to view this content.

[rizmomin]

Hi Jindon:

When i run the above macro with my sheet, It gives Runtime error 1004.
When i debug it highlights

Please Login or Register  to view this content.

My data is in sheet called : "Daily_Receipt" and 5/1/2013 is in cell O47
Data range would be O47:EG78. This will be for the whole month.
Above and below this range, there will be some other data which i am not interested.

I want to paste full month of data at A2 in Sheet "EmpClock" instead of creating a new sheet.
Also want to make sure that the data pasted is the paste.special value. (Source data has formulas)

Please LET ME KNOW IF ANY QUESTIONS.

Thank you

Riz Momin

[jindon]

Try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[rizmomin]

Hi Jindon:

It still gives RUntime Error.
I am using below code

Please Login or Register  to view this content.

Please help

Riz Momin

[jindon]

If the sheet doesn't look like the attached, need to see your data layouts.

[rizmomin]

Hi jindon:

My file is too big to show you (8MB).
The data you have showed is exactly like i have in my sheet.
I just noticed that if I manually go into "Daily_Receipt" sheet and select the data to copy, after leaving that sheet, the paste icon is greyed out, so i can't manaully paste.
This could be the reason why it is not working for me.

Please help and let me know if any questions.

Thank you

Riz Momin

[AB33]

It is not fast as Jindon's code

[jindon]

What kind of Error and which line?

[jindon]

Hi Jindon:

When i run the above macro with my sheet, It gives Runtime error 1004.
When i debug it highlights

Please Login or Register  to view this content.

Try delete that line

Please Login or Register  to view this content.

[rizmomin]

Hi Jindon:

Sorry i was out of town and just got back.
Please refer to the attached sheet which i use your code and see the error, it is giving.
Please help and let me know if any questions.

Thank you

Riz Momin

[jindon]

I can not open your file saying

"Found problem that might give serious damage to your computer"

[rizmomin]

Hi:

I am not sure why it would not open at your end.
Its opening fine with me.
I have attached the code and the error line.
Let me tell you that the code worked for couple of times and gave the error when new data is pasted.

Right now the error is at

Please Login or Register  to view this content.

and when you hoover over the value it gives <<Subscript out of Range >>
in above code, the value of n=931
Could be that value is not resetting when macro is run again.


Please have a look and let me know ..
I really appreciate your help.

[jindon]

Then try chnge

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and let's see...

[rizmomin]

Hi jindon:

Still same problem.
The value of n=931

Please help and let me know if any questions.

Thank you

Riz Momin

[rizmomin]

Hi Jindon:

Please find attached 2 screen shot.
Let me know if any questions.

Thank you

Riz Momin

[nilem]

Hi Riz
Try to change the Jindon's code (if Jindon does not mind)
as applied at the file 'Employee-ClockinOut.xls‎'

Please Login or Register  to view this content.

[rizmomin]

Hi Nilem:

Thank you so much for your great help.
It works but 1 problem.
When i run the macro, it deletes the source data..i do not want that.

Please help and let me know if any questions.

Thank you

Riz Momin

[jindon]

I said if your sheet looks exactly the same as my last uploaded sheet....

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[rizmomin]

Hello jinDon and Nilem:

I have modified the code as below and works like a charm.

Please Login or Register  to view this content.

I am very grateful to both of you for your time affort.
Thank you so much

Riz Momin

[rizmomin]

Hi Nilem & JIndon:

I am seeing the same problem again as earlier.
It works for some time and then it gives "Runtime error" as earlier.

Please help and let me know if any questions.

Thank you

Riz Momin

[nilem]

maybe change this line

Please Login or Register  to view this content.

or show a file in which the error occurs

[jindon]

If you don't follow my suggestion, don't bother.

[rizmomin]

Hi Jindon & Nilem:

Sorry for the confusion.
I have cleared and started all over again and looks like it looks now.
I am very grateful for your help.

Thank you

Riz Momin