aakhan2011
New Member
- Joined
- Jan 12, 2014
- Messages
- 21
I want some vbs code on my 'Copy All' button in Email Data sheet which will do my work. Its a bit lengthy but who knows the concept will find it easy. As i am a newbie help is greatly required.
I have one sheets -
Sheet attached -
Worksheet .xlsm - Speedy Share - upload your files here
Workshet.xlsm [Data in 'Mod' sheet i)should convert from text to column and paste in 'Row Data' sheet ii) then transfer form Row Data sheet to 'Email Data' sheet 'Mobile Data' sheet and 'Other Data' sheet when Copy All button is clicked in Email Data sheet.
There should be some logic which will fetch appropriate date from 'Mod' sheet to the respective sheet
We can have some kind of logic like
If 'Register' found then take the value of register and workstation and paste in Email Data sheet
All data below should be fetched from Row Data sheet which is blank now and will get the data when we click the Copy All button ,
1st Register Count:
Below Register Count (*) i.e. 16 If workstation count present copy to E15 in Email Data sheet in Table B
Below Register Count (*) i.e. 9 If register count present copy to E16 in Email Data sheet Table B
2nd Register Count:
Below Register Count (*) i.e. 8942 If workstation count present copy to C15 in Email Data sheet in Table B
Below Register Count (*) i.e. 2883 If register count present copy to C16 in Email Data sheet in Table B
3rd Register Count:
Below Register Count (*) i.e. 0 If workstation count present copy to E18 in Email Data sheet in Table B
Below Register Count (*) i.e. 1 If register count present copy to E19 in Email Data sheet in Table B
4th Register Count:
Below Register Count (*) i.e. 98 If workstation count present copy to C18 in Email Data sheet in Table B
Below Register Count (*) i.e. 999 If register count present copy to C19 in Email Data sheet in Table B
Substr:
B32:B44 will be pasted in C29:C41 in Email Data sheet in Table C
i.e. 10 to 22 values should match in corresponding 10 to 22 values in Email Data sheet , if not not any value then 0
B50:B62 will be pasted in B29:B41 in Email Data sheet in Table C
i.e. 10 to 22 values should match in corresponding 10 to 22 values in Email Data sheet , if not not any value then 0
B68:B80 will be pasted in E29:E41 in Email Data sheet in Table C
i.e. 10 to 22 values should match in corresponding 10 to 22 values in Email Data sheet , if not not any value then 0
B86:B97 will be pasted in D29:D41 in Email Data sheet in Table C
i.e. 10 to 22 values should match in corresponding 10 to 22 values in Email Data sheet , if not not any value then 0
A103:A106 will be pasted in B48:B52 in Email Data sheet in Table D
A112 will be pasted in A9 in Table A
A118 will be pasted in D9 in Table A
A124 will be pasted in E9 in Table A
A130 and A131 complete row to be pasted in Email Data sheet in Table E
The count of A130 and A131 i.e. 2 will be pasted in K9 in Email Data sheet in Table A
If any more details required, then ask.
Help will be greatly appreciated
I have one sheets -
Sheet attached -
Worksheet .xlsm - Speedy Share - upload your files here
Workshet.xlsm [Data in 'Mod' sheet i)should convert from text to column and paste in 'Row Data' sheet ii) then transfer form Row Data sheet to 'Email Data' sheet 'Mobile Data' sheet and 'Other Data' sheet when Copy All button is clicked in Email Data sheet.
There should be some logic which will fetch appropriate date from 'Mod' sheet to the respective sheet
We can have some kind of logic like
If 'Register' found then take the value of register and workstation and paste in Email Data sheet
All data below should be fetched from Row Data sheet which is blank now and will get the data when we click the Copy All button ,
1st Register Count:
Below Register Count (*) i.e. 16 If workstation count present copy to E15 in Email Data sheet in Table B
Below Register Count (*) i.e. 9 If register count present copy to E16 in Email Data sheet Table B
2nd Register Count:
Below Register Count (*) i.e. 8942 If workstation count present copy to C15 in Email Data sheet in Table B
Below Register Count (*) i.e. 2883 If register count present copy to C16 in Email Data sheet in Table B
3rd Register Count:
Below Register Count (*) i.e. 0 If workstation count present copy to E18 in Email Data sheet in Table B
Below Register Count (*) i.e. 1 If register count present copy to E19 in Email Data sheet in Table B
4th Register Count:
Below Register Count (*) i.e. 98 If workstation count present copy to C18 in Email Data sheet in Table B
Below Register Count (*) i.e. 999 If register count present copy to C19 in Email Data sheet in Table B
Substr:
B32:B44 will be pasted in C29:C41 in Email Data sheet in Table C
i.e. 10 to 22 values should match in corresponding 10 to 22 values in Email Data sheet , if not not any value then 0
B50:B62 will be pasted in B29:B41 in Email Data sheet in Table C
i.e. 10 to 22 values should match in corresponding 10 to 22 values in Email Data sheet , if not not any value then 0
B68:B80 will be pasted in E29:E41 in Email Data sheet in Table C
i.e. 10 to 22 values should match in corresponding 10 to 22 values in Email Data sheet , if not not any value then 0
B86:B97 will be pasted in D29:D41 in Email Data sheet in Table C
i.e. 10 to 22 values should match in corresponding 10 to 22 values in Email Data sheet , if not not any value then 0
A103:A106 will be pasted in B48:B52 in Email Data sheet in Table D
A112 will be pasted in A9 in Table A
A118 will be pasted in D9 in Table A
A124 will be pasted in E9 in Table A
A130 and A131 complete row to be pasted in Email Data sheet in Table E
The count of A130 and A131 i.e. 2 will be pasted in K9 in Email Data sheet in Table A
If any more details required, then ask.
Help will be greatly appreciated
