The macro is functioning as per my expectation.
Please set the calculation to automatic in your workbook if you hv not done so.
R u getting any error. If so what is the error.
Goto into the debug mode and check if the values in empcode is changing.
On Wed, Jul 6, 2011 at 5:16 PM, ankur <ankurpande...@gmail.com> wrote:
> HI VASANT
> THNKS FOR UR EFFORTS
> EmpCode = ThisWorkbook.Worksheets("Sheet2").Cells(x, 1)..........this code
> is not working....
> U R GETTING IN RIGHT DIRECTION, NOW I NEED... THE STAFF NO OF NEXT EMPLOYEE
> GET CHANGED AND THE RESULT GET PASTED IN SHEET2....MEANS AUTO CHANGING OF
> DATA VALIDATION HAPPENS...AND RESULT GET PASTED IN THE SHEETS
>
> thnks
> Have A Nice Time & Enjoy Life
>
> Regards:
> CMA Ankur Pandey
> (Someone Different)
>
> I'm not the best but i'm not like the rest~~
>
>
> On Wed, Jul 6, 2011 at 3:00 PM, Vasant <vasant...@gmail.com> wrote:
>>
>> Hi Ankur,
>>
>> Not sure, whether i understood your query completly...
>>
>> Pls find this code
>>
>> This code takes the staff no. from Sheet2 and puts in "A2" in the
>> 'gents report" sheet. It then picks up the result from "C2" cell
>> of the same sheet and puts into the income tax column for the
>> corresponding staff code.
>>
>>
>> Sub Test()
>> Lrow = ThisWorkbook.Worksheets("Sheet2").Range("A65536").End(xlUp).Row
>> For x = 2 To Lrow
>> EmpCode = ThisWorkbook.Worksheets("Sheet2").Cells(x, 1)
>> ThisWorkbook.Worksheets("gents report").Range("A2") = EmpCode
>> It = ThisWorkbook.Worksheets("gents report").Range("C2")
>> ThisWorkbook.Worksheets("Sheet2").Cells(x, 3) = It
>> Next x
>> End Sub
>>
>>
>> On Wed, Jul 6, 2011 at 1:20 PM, ankur < ankurpande...@gmail.com> wrote:
>> > HI VASANT
>> > PLZ FIND THE ATTACHED FILE
>> > this is sample file...full data can't be attached becoz of file
>> > size.....
>> >
>> > Have A Nice Time & Enjoy Life
>> >
>> > Regards:
>> > CMA Ankur Pandey
>> > (Someone Different)
>> >
>> > I'm not the best but i'm not like the rest~~
>> >
>> >
>> > On Wed, Jul 6, 2011 at 12:49 PM, Vasant <vasant...@gmail.com> wrote:
>> >>
>> >> Hi Ankur,
>> >>
>> >> Glad to help you. Pls send me your file...let me try :)
>> >>
>> >> On Wed, Jul 6, 2011 at 12:34 PM, ankur <ankurpande...@gmail.com> wrote:
>> >> > hi vasant
>> >> > thnks for such nice code
>> >> > i have similar problem also...but i need some modification
>> >> > this code produce many workbook...cant it be possible to create a
>> >> > single
>> >> > workbook with different sheets for each employee.... ?
>> >> >
>> >> > another query
>> >> >
>> >> > i have asked one query earlier about data validation requirement, can
>> >> > u
>> >> > help
>> >> > me in that also
>> >> > im just giving my requirement.....plz help me
>> >> > i have a workbook for calculating income tax of employees with many
>> >> > sheets,,,,,different sheets are having monthwise data of
>> >> > employees....one
>> >> > sheet for their saving particulars....one main sheet in which all the
>> >> > calculation are done...and their net tax is calculated by getting
>> >> > data
>> >> > from
>> >> > the sheets....like form 16
>> >> > i used data valiation in the to get details of each employee in the
>> >> > main
>> >> > sheet( by linking with vlookup and other formulas).... i need one
>> >> > report
>> >> > which show
>> >> > net income tax of each employee in one sheet...like
>> >> > employee name---income tax
>> >> > aaaa 25415
>> >> > bbbb 5486
>> >> >
>> >> > for that i have to manually change the data validation list from drop
>> >> > down...the income tax which came , i copy and paste in other
>> >> > sheet.....i
>> >> > have to do this for all the employees.....this take lot of time
>> >> >
>> >> > i want just by one click i got all the income tax data of all the
>> >> > employees
>> >> > whose name is in data validation list....in another sheet.....
>> >> >
>> >> > can u help me...?
>> >> >
>> >> > Have A Nice Time & Enjoy Life
>> >> >
>> >> > Regards:
>> >> > CMA Ankur Pandey
>> >> > (Someone Different)
>> >> >
>> >> > I'm not the best but i'm not like the rest~~
>> >> >
>> >> >
>> >> > On Wed, Jul 6, 2011 at 11:45 AM, Vasant <vasant...@gmail.com> wrote:
>> >> >>
>> >> >> Hi Jai,
>> >> >>
>> >> >> Pls find the below code and the file attached.
>> >> >>
>> >> >> The code here creates a new workbook for each employee with their
>> >> >> details and saves them with their name and code.
>> >> >>
>> >> >>
>> >> >> Sub Test()
>> >> >> Dim NwWkBk As Workbook
>> >> >> Lrow = ThisWorkbook.Worksheets(2).Range("A65536").End(xlUp).Row
>> >> >> For x = 2 To Lrow
>> >> >> Set NwWkBk = Workbooks.Add
>> >> >> ThisWorkbook.Worksheets(1).Cells.Copy
>> >> >> NwWkBk.Worksheets(1).Activate
>> >> >> ActiveSheet.Paste
>> >> >> ActiveSheet.Range("A1").EntireColumn.ColumnWidth =
>> >> >> ThisWorkbook.Worksheets(1).Range("A1").ColumnWidth
>> >> >> ActiveSheet.Range("B1").EntireColumn.ColumnWidth =
>> >> >> ThisWorkbook.Worksheets(1).Range("B1").ColumnWidth
>> >> >> ActiveSheet.Range("C1").EntireColumn.ColumnWidth =
>> >> >> ThisWorkbook.Worksheets(1).Range("C1").ColumnWidth
>> >> >> ActiveSheet.Range("D1").EntireColumn.ColumnWidth =
>> >> >> ThisWorkbook.Worksheets(1).Range("D1").ColumnWidth
>> >> >>
>> >> >> NwWkBk.Worksheets(1).Range("B6") =
>> >> >> ThisWorkbook.Worksheets(2).Range("B" & Trim(Str(x)))
>> >> >> NwWkBk.Worksheets(1).Range("B7") =
>> >> >> ThisWorkbook.Worksheets(2).Range("C" & Trim(Str(x))) & " " &
>> >> >> ThisWorkbook.Worksheets(2).Range("A" & Trim(Str(x)))
>> >> >> NwWkBk.SaveAs ThisWorkbook.Worksheets(2).Range("B" & Trim(Str(x))) &
>> >> >> "(" & ThisWorkbook.Worksheets(2).Range("A" & Trim(Str(x))) & ")"
>> >> >> Next x
>> >> >> End Sub
>> >> >>
>> >> >>
>> >> >> On Wed, Jul 6, 2011 at 10:45 AM, Jai <jaihumtu...@gmail.com> wrote:
>> >> >> > Dear Experts
>> >> >> >
>> >> >> > i wnat to mail merge From Sheet -2 To Sheet -1, in Sheet -2 ,
>> >> >> > Name,
>> >> >> > Desination, & Emp code .
>> >> >> >
>> >> >> > --
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > ----------------------------------------------------------------------------------
>> >> >> > Some important links for excel users:
>> >> >> > 1. Follow us on TWITTER for tips tricks and links :
>> >> >> > http://twitter.com/exceldailytip
>> >> >> > 2. Join our LinkedIN group @
>> >> >> > http://www.linkedin.com/groups?gid=1871310
>> >> >> > 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> >> >> > 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> >> >> > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>> >> >> >
>> >> >> > To post to this group, send email to excel-macros@googlegroups.com
>> >> >> >
>> >> >> > <><><><><><><><><><><><><><><><><><><><><><>
>> >> >> > Like our page on facebook , Just follow below link
>> >> >> > http://www.facebook.com/discussexcel
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Regards
>> >> >>
>> >> >> Vasant
>> >> >>
>> >> >> skype Id: vasantjob
>> >> >> vasant...@gmail.com
>> >> >>
>> >> >> --
>> >> >>
>> >> >>
>> >> >>
>> >> >> ----------------------------------------------------------------------------------
>> >> >> Some important links for excel users:
>> >> >> 1. Follow us on TWITTER for tips tricks and links :
>> >> >> http://twitter.com/exceldailytip
>> >> >> 2. Join our LinkedIN group @
>> >> >> http://www.linkedin.com/groups?gid=1871310
>> >> >> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> >> >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> >> >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>> >> >>
>> >> >> To post to this group, send email to excel-macros@googlegroups.com
>> >> >>
>> >> >> <><><><><><><><><><><><><><><><><><><><><><>
>> >> >> Like our page on facebook , Just follow below link
>> >> >> http://www.facebook.com/discussexcel
>> >> >
>> >> > --
>> >> >
>> >> >
>> >> > ----------------------------------------------------------------------------------
>> >> > Some important links for excel users:
>> >> > 1. Follow us on TWITTER for tips tricks and links :
>> >> > http://twitter.com/exceldailytip
>> >> > 2. Join our LinkedIN group @
>> >> > http://www.linkedin.com/groups?gid=1871310
>> >> > 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> >> > 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> >> > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>> >> >
>> >> > To post to this group, send email to excel-macros@googlegroups.com
>> >> >
>> >> > <><><><><><><><><><><><><><><><><><><><><><>
>> >> > Like our page on facebook , Just follow below link
>> >> > http://www.facebook.com/discussexcel
>> >> >
>> >>
>> >>
>> >>
>> >> --
>> >> Regards
>> >>
>> >> Vasant
>> >>
>> >> skype Id: vasantjob
>> >> http://facebook.com/vasantjob
>> >>
>> >> --
>> >>
>> >>
>> >> ----------------------------------------------------------------------------------
>> >> Some important links for excel users:
>> >> 1. Follow us on TWITTER for tips tricks and links :
>> >> http://twitter.com/exceldailytip
>> >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> >> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>> >>
>> >> To post to this group, send email to excel-macros@googlegroups.com
>> >>
>> >> <><><><><><><><><><><><><><><><><><><><><><>
>> >> Like our page on facebook , Just follow below link
>> >> http://www.facebook.com/discussexcel
>> >
>> > --
>> >
>> > ----------------------------------------------------------------------------------
>> > Some important links for excel users:
>> > 1. Follow us on TWITTER for tips tricks and links :
>> > http://twitter.com/exceldailytip
>> > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> > 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> > 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>> >
>> > To post to this group, send email to excel-macros@googlegroups.com
>> >
>> > <><><><><><><><><><><><><><><><><><><><><><>
>> > Like our page on facebook , Just follow below link
>> > http://www.facebook.com/discussexcel
>> >
>>
>>
>>
>> --
>> Regards
>>
>> Vasant
>>
>> skype Id: vasantjob
>> http://facebook.com/vasantjob
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to
>> excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><> <><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>
> --
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
Regards
Vasant
skype Id: vasantjob
http://facebook.com/vasantjob
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
