from:"Anish Shrivastava"

Sure Harish :) ..

You will have it soon..

On Thu, Jul 21, 2011 at 3:45 PM, Harish Sharma harishsharma...@gmail.comwrote:

 Can someone help this is really urgent.

 Thanks
 Harish


 On 20 July 2011 23:06, Harish Sharma harishsharma...@gmail.com wrote:

 Hi Anish,

 Please find attached the sample file with one row filled.
 And Column C  Column H concatenate should give us unique value so if
 this is repeated then we need to have below requested feature.

 Hope this would be more clear now.

 Thanks
 Harish



 On 20 July 2011 21:32, Anish Shrivastava anish@gmail.com wrote:

 One sample sheet would be helpful to us to see the data structure.

 However, for this *Also identifiy if there is any row duplicated and
 highlight them and paste them into another sheet * There must be any
 particular data field which has to be checked against others in order to
 find duplicates.

 Macro for consolidation is not a big deal.


 On Wed, Jul 20, 2011 at 7:35 PM, Harish Sharma 
 harishsharma...@gmail.com wrote:

 Hi,

 I had a macro file which was able to consolidate small files but now I
 have a requirement to consolidate 30 workbooks saved in a share drive and
 this data may even cross 66000 row after consolidation. Can someone help me
 in creating a macro which picks all the file from shared folder and
 consolidates the one specific worksheet in all the workbook.

 This macro should also be capable of deleting any blank row in any of
 these 30 workbooks so that we donot miss any data if someone by error has
 missed a couple of row and entered the data in couple of row below.

 30 workbooks in sharedrive
 one specific worksheet from all of these named as Tracker
 Should cover all the blanks and delete them so that no data missed
 Also identifiy if there is any row duplicated and highlight them and
 paste them into another sheet
 If data crosses excel 2003 limit of row it should move to next
 worksheet.

 Hope to get a solution from excel experts.

 --
 Kind Regards,
 Harish Sharma

 --

 --
 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




 --
 Kind Regards,
 Harish Sharma




 --
 Kind Regards,
 Harish Sharma

 --

 --
 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

Re: $$Excel-Macros$$ Workbooks Consolidation Macro

Harish,

I forgot to ask you one thing.
The column headers in the file you attached will be constant for all the
30-35 files which are there on your sharedrive, right?
Cheers,
Anish
On Wed, Jul 20, 2011 at 11:06 PM, Harish Sharma
harishsharma...@gmail.comwrote:

 Hi Anish,

 Please find attached the sample file with one row filled.
 And Column C  Column H concatenate should give us unique value so if
 this is repeated then we need to have below requested feature.

 Hope this would be more clear now.

 Thanks
 Harish



 On 20 July 2011 21:32, Anish Shrivastava anish@gmail.com wrote:

 One sample sheet would be helpful to us to see the data structure.

 However, for this *Also identifiy if there is any row duplicated and
 highlight them and paste them into another sheet * There must be any
 particular data field which has to be checked against others in order to
 find duplicates.

 Macro for consolidation is not a big deal.


 On Wed, Jul 20, 2011 at 7:35 PM, Harish Sharma harishsharma...@gmail.com
  wrote:

 Hi,

 I had a macro file which was able to consolidate small files but now I
 have a requirement to consolidate 30 workbooks saved in a share drive and
 this data may even cross 66000 row after consolidation. Can someone help me
 in creating a macro which picks all the file from shared folder and
 consolidates the one specific worksheet in all the workbook.

 This macro should also be capable of deleting any blank row in any of
 these 30 workbooks so that we donot miss any data if someone by error has
 missed a couple of row and entered the data in couple of row below.

 30 workbooks in sharedrive
 one specific worksheet from all of these named as Tracker
 Should cover all the blanks and delete them so that no data missed
 Also identifiy if there is any row duplicated and highlight them and
 paste them into another sheet
 If data crosses excel 2003 limit of row it should move to next worksheet.

 Hope to get a solution from excel experts.

 --
 Kind Regards,
 Harish Sharma

 --

 --
 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




 --
 Kind Regards,
 Harish Sharma

 --

 --
 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

Re: $$Excel-Macros$$ Workbooks Consolidation Macro

Hi Harsih,

Since you didnt reply about column headers I assumed that it's the same for
every file on share drive.

Please see the attached file with macro.

You must populate the file path in Sheet2-A1.

Do let me know if it works fine for you.
Cheers!!
Anish
On Thu, Jul 21, 2011 at 4:00 PM, Anish Shrivastava anish@gmail.comwrote:

 Harish,

 I forgot to ask you one thing.
 The column headers in the file you attached will be constant for all the
 30-35 files which are there on your sharedrive, right?
 Cheers,
 Anish
   On Wed, Jul 20, 2011 at 11:06 PM, Harish Sharma 
 harishsharma...@gmail.com wrote:

 Hi Anish,

 Please find attached the sample file with one row filled.
 And Column C  Column H concatenate should give us unique value so if
 this is repeated then we need to have below requested feature.

 Hope this would be more clear now.

 Thanks
 Harish



 On 20 July 2011 21:32, Anish Shrivastava anish@gmail.com wrote:

 One sample sheet would be helpful to us to see the data structure.

 However, for this *Also identifiy if there is any row duplicated and
 highlight them and paste them into another sheet * There must be any
 particular data field which has to be checked against others in order to
 find duplicates.

 Macro for consolidation is not a big deal.


 On Wed, Jul 20, 2011 at 7:35 PM, Harish Sharma 
 harishsharma...@gmail.com wrote:

 Hi,

 I had a macro file which was able to consolidate small files but now I
 have a requirement to consolidate 30 workbooks saved in a share drive and
 this data may even cross 66000 row after consolidation. Can someone help me
 in creating a macro which picks all the file from shared folder and
 consolidates the one specific worksheet in all the workbook.

 This macro should also be capable of deleting any blank row in any of
 these 30 workbooks so that we donot miss any data if someone by error has
 missed a couple of row and entered the data in couple of row below.

 30 workbooks in sharedrive
 one specific worksheet from all of these named as Tracker
 Should cover all the blanks and delete them so that no data missed
 Also identifiy if there is any row duplicated and highlight them and
 paste them into another sheet
 If data crosses excel 2003 limit of row it should move to next
 worksheet.

 Hope to get a solution from excel experts.

 --
 Kind Regards,
 Harish Sharma

 --

 --
 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




 --
 Kind Regards,
 Harish Sharma

 --

 --
 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


Sample_Sheet_Harish_Solved_Anish.xlsm
Description: Binary data

Re: $$Excel-Macros$$ Workbooks Consolidation Macro

Harish,

PLease ignore the earlier attachment as I forgot to add *DUPLICATE CHECK* in
that macro.

Use this attachment.

On Thu, Jul 21, 2011 at 5:09 PM, Anish Shrivastava anish@gmail.comwrote:

 Hi Harsih,

 Since you didnt reply about column headers I assumed that it's the same for
 every file on share drive.

 Please see the attached file with macro.

 You must populate the file path in Sheet2-A1.

 Do let me know if it works fine for you.
 Cheers!!
 Anish
   On Thu, Jul 21, 2011 at 4:00 PM, Anish Shrivastava 
 anish@gmail.comwrote:

 Harish,

 I forgot to ask you one thing.
 The column headers in the file you attached will be constant for all the
 30-35 files which are there on your sharedrive, right?
 Cheers,
 Anish
   On Wed, Jul 20, 2011 at 11:06 PM, Harish Sharma 
 harishsharma...@gmail.com wrote:

 Hi Anish,

 Please find attached the sample file with one row filled.
 And Column C  Column H concatenate should give us unique value so if
 this is repeated then we need to have below requested feature.

 Hope this would be more clear now.

 Thanks
 Harish



 On 20 July 2011 21:32, Anish Shrivastava anish@gmail.com wrote:

 One sample sheet would be helpful to us to see the data structure.

 However, for this *Also identifiy if there is any row duplicated and
 highlight them and paste them into another sheet * There must be any
 particular data field which has to be checked against others in order to
 find duplicates.

 Macro for consolidation is not a big deal.


 On Wed, Jul 20, 2011 at 7:35 PM, Harish Sharma 
 harishsharma...@gmail.com wrote:

 Hi,

 I had a macro file which was able to consolidate small files but now I
 have a requirement to consolidate 30 workbooks saved in a share drive and
 this data may even cross 66000 row after consolidation. Can someone help 
 me
 in creating a macro which picks all the file from shared folder and
 consolidates the one specific worksheet in all the workbook.

 This macro should also be capable of deleting any blank row in any of
 these 30 workbooks so that we donot miss any data if someone by error has
 missed a couple of row and entered the data in couple of row below.

 30 workbooks in sharedrive
 one specific worksheet from all of these named as Tracker
 Should cover all the blanks and delete them so that no data missed
 Also identifiy if there is any row duplicated and highlight them and
 paste them into another sheet
 If data crosses excel 2003 limit of row it should move to next
 worksheet.

 Hope to get a solution from excel experts.

 --
 Kind Regards,
 Harish Sharma

 --

 --
 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




 --
 Kind Regards,
 Harish Sharma

 --

 --
 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

Re: $$Excel-Macros$$ help requ

Vijay,

Though you have provided the sample sheet your requirement is not clear.

What do you mean by merge the names in one sheet. do you want to consolidate
the data or you want a concatenation.

On Thu, Jul 21, 2011 at 5:33 PM, vijayajith VA vijayajith...@gmail.comwrote:

 fyi


 On Wed, Jul 20, 2011 at 6:25 PM, NOORAIN ANSARI 
 noorain.ans...@gmail.comwrote:

 Dear Vijay,

 Please provide sample sheet

   On Wed, Jul 20, 2011 at 5:49 PM, vijayajith VA vijayajith...@gmail.com
  wrote:

 Hi,


 1;
  I have several names in two sheets..i need to merge it in one sheet...is
 this possible using formula..

 Thanks



 --

 --
 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




 --
 Thanks  regards,
 Noorain Ansari
 *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.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


-- 
--
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

Re: $$Excel-Macros$$ Re: Formula for find and replace

Giving the formula as per your  example.

*=IF(RIGHT(A1,3)=ABC,LEFT(A1,2)#,A1)*

Copy it down.

On Thu, Jul 21, 2011 at 8:55 PM, Prabhu prabhugate...@gmail.com wrote:

 Hi Friends,

 Can  you help me to know the formula for find and replace.Example i need to
 find  ABC in the A1 cell and replace with #. If there is no 'ABC
  existing A1 should remain as it is.(Not using ctr+H)

 *Example.*

 Existing Cell  Replace as
 12ABC   12#

 13XYZ13XYZ

 --

 --
 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

Re: $$Excel-Macros$$ Re: Formula for find and replace

Prabhu,

Use the solution given by GoldenLance below.

=SUBSTITUTE(A1,ABC,#)
*GoldenLance,*
Thanks Man, This formula didnt hit my mind at that time.

On Thu, Jul 21, 2011 at 10:02 PM, GoldenLance samde...@gmail.com wrote:

 Wouldn't this be much more easier, and apt?

 =SUBSTITUTE(A1,ABC,#)

 On Jul 21, 9:27 pm, Prabhu prabhugate...@gmail.com wrote:
  Hi Anish,
 
  If it is ABC12 and i need to replace the ABC form the cell then the
  formula will be ?
 
  Regards,
 
  Prabhu

 --

 --
 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

Re: $$Excel-Macros$$ Workbooks Consolidation Macro

Harish,

Did you try it.. ??

On Thu, Jul 21, 2011 at 5:19 PM, Anish Shrivastava anish@gmail.comwrote:

 Harish,

 PLease ignore the earlier attachment as I forgot to add *DUPLICATE CHECK*in 
 that macro.

 Use this attachment.

   On Thu, Jul 21, 2011 at 5:09 PM, Anish Shrivastava 
 anish@gmail.comwrote:

 Hi Harsih,

 Since you didnt reply about column headers I assumed that it's the same
 for every file on share drive.

 Please see the attached file with macro.

 You must populate the file path in Sheet2-A1.

 Do let me know if it works fine for you.
 Cheers!!
 Anish
   On Thu, Jul 21, 2011 at 4:00 PM, Anish Shrivastava anish@gmail.com
  wrote:

 Harish,

 I forgot to ask you one thing.
 The column headers in the file you attached will be constant for all the
 30-35 files which are there on your sharedrive, right?
 Cheers,
 Anish
   On Wed, Jul 20, 2011 at 11:06 PM, Harish Sharma 
 harishsharma...@gmail.com wrote:

 Hi Anish,

 Please find attached the sample file with one row filled.
 And Column C  Column H concatenate should give us unique value so
 if this is repeated then we need to have below requested feature.

 Hope this would be more clear now.

 Thanks
 Harish



 On 20 July 2011 21:32, Anish Shrivastava anish@gmail.com wrote:

 One sample sheet would be helpful to us to see the data structure.

 However, for this *Also identifiy if there is any row duplicated and
 highlight them and paste them into another sheet * There must be any
 particular data field which has to be checked against others in order to
 find duplicates.

 Macro for consolidation is not a big deal.


 On Wed, Jul 20, 2011 at 7:35 PM, Harish Sharma 
 harishsharma...@gmail.com wrote:

 Hi,

 I had a macro file which was able to consolidate small files but now I
 have a requirement to consolidate 30 workbooks saved in a share drive and
 this data may even cross 66000 row after consolidation. Can someone help 
 me
 in creating a macro which picks all the file from shared folder and
 consolidates the one specific worksheet in all the workbook.

 This macro should also be capable of deleting any blank row in any of
 these 30 workbooks so that we donot miss any data if someone by error has
 missed a couple of row and entered the data in couple of row below.

 30 workbooks in sharedrive
 one specific worksheet from all of these named as Tracker
 Should cover all the blanks and delete them so that no data missed
 Also identifiy if there is any row duplicated and highlight them and
 paste them into another sheet
 If data crosses excel 2003 limit of row it should move to next
 worksheet.

 Hope to get a solution from excel experts.

 --
 Kind Regards,
 Harish Sharma

 --

 --
 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




 --
 Kind Regards,
 Harish Sharma

 --

 --
 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

Re: $$Excel-Macros$$ Help Macro doesn´t work anymore, keeps giving same error

Please check if the sheet name is Pivot.
if not then rename it to Pivot.
On Thu, Jul 21, 2011 at 10:44 PM, Jorge Marques leote.w...@gmail.comwrote:

 Hi guys, i have this macro i use to copy a range from column D of a
 worksheet of a workbook to column G of another workbook and worksheet, but
 it keeps giving me the same error saying that it´s subscript out of range
 in the line in yellow.

 Public wb, wbmes As Workbook
 Sub filldatabase()
 Call AbrirFile
 Call left
 wbmes.Close
 End Sub
 Private Sub AbrirFile()
 Dim Filter, Caption As String
 Dim SelectedFile As Variant
 Set wb = ThisWorkbook

 Filter = Ficheiro XLS (*.xls),(*.xls)
 Caption = Escolha o ficheiro a importar...
 SelectedFile = Application.GetOpenFilename(Filter, , Caption)
 If SelectedFile = False Then Exit Sub
 Set wbmes = Workbooks.Open(SelectedFile, 1, 1)
 End Sub

 Private Sub left()
 Dim a As Double
 wbmes.Activate
 wbmes.Sheets(Total_Refrige).Select
 wbmes.Sheets(Total_Refrige).Range(Range(D2),
 Range(D2).End(xlDown)).Copy
 wb.Activate
 If wb.Sheets(Pivot).Range(G2).Value   Then
 wb.Sheets(Pivot).Range(G1).End(xlDown).Offset(1, 0).Select
 Else
 wb.Sheets(Pivot).Range(G2).Select
 End If
 a = ActiveCell.Row
 wb.Sheets(Total Refrige).Paste
 Application.CutCopyMode = False
 i = a
 While wb.Sheets(Pivot).Range(G  i).Value  
 i = i + 1

 Wend
 End Sub

 Do you have any idea how to solve this?1000 thanks

 --

 --
 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

Re: $$Excel-Macros$$ Help Macro doesn´t work anymore, keeps giving same error

is it possible to attach the sheet? You can remove the data from it or
replace the values..

On Thu, Jul 21, 2011 at 11:04 PM, Jorge Marques leote.w...@gmail.comwrote:

 Yes, the names are all ok, i double checked it, even try to change names,
 but doesn´t work :S

   2011/7/21 Anish Shrivastava anish@gmail.com

 Please check if the sheet name is Pivot.
 if not then rename it to Pivot.
   On Thu, Jul 21, 2011 at 10:44 PM, Jorge Marques 
 leote.w...@gmail.comwrote:

 Hi guys, i have this macro i use to copy a range from column D of a
 worksheet of a workbook to column G of another workbook and worksheet, but
 it keeps giving me the same error saying that it´s subscript out of range
 in the line in yellow.

 Public wb, wbmes As Workbook
 Sub filldatabase()
 Call AbrirFile
 Call left
 wbmes.Close
 End Sub
 Private Sub AbrirFile()
 Dim Filter, Caption As String
 Dim SelectedFile As Variant
 Set wb = ThisWorkbook

 Filter = Ficheiro XLS (*.xls),(*.xls)
 Caption = Escolha o ficheiro a importar...
 SelectedFile = Application.GetOpenFilename(Filter, , Caption)
 If SelectedFile = False Then Exit Sub
 Set wbmes = Workbooks.Open(SelectedFile, 1, 1)
 End Sub

 Private Sub left()
 Dim a As Double
 wbmes.Activate
 wbmes.Sheets(Total_Refrige).Select
 wbmes.Sheets(Total_Refrige).Range(Range(D2),
 Range(D2).End(xlDown)).Copy
 wb.Activate
 If wb.Sheets(Pivot).Range(G2).Value   Then
 wb.Sheets(Pivot).Range(G1).End(xlDown).Offset(1, 0).Select
 Else
 wb.Sheets(Pivot).Range(G2).Select
 End If
 a = ActiveCell.Row
 wb.Sheets(Total Refrige).Paste
 Application.CutCopyMode = False
 i = a
 While wb.Sheets(Pivot).Range(G  i).Value  
 i = i + 1

 Wend
 End Sub

 Do you have any idea how to solve this?1000 thanks

 --

 --
 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


 --

 --
 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

Re: $$Excel-Macros$$ Automatic Outlook message

Yes Prabhu, It is possible.. Pour in your further details/sample
attachments.

On Thu, Jul 21, 2011 at 11:03 PM, Prabhu prabhugate...@gmail.com wrote:

 Hi all,

 I need to send Automatic mail message from my mail id to some around 10
 members in every week end to remind them to do something.

 I can create distribution list for whom i have to send mails, Message
 content will be same only.

 It needs to reach every week end to that particular distribution list
 members.

 Is there any way to send automatic reminder mail ?

 Plz help.

 Regards

 Prabhu

 --

 --
 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

Re: $$Excel-Macros$$ I'm in budget vlookup hell. Please help

Hi Adam,

Looking at your workbook, it seems that it's a complete dashboard project to
be completed and in these cases Sample data wouldn't do the perfect job
(However we can create one dummy dashboard).

I understand that you want it to be interactive to the user creating
homepage and navigation with charts and other analysis.

I have one question for you, why are you using images(charts) on the sheet.
where as you can do it with excel charts itself.

Your project is a big one and if we have to do it, it might take some time
to go through all the sheets and understand the data structure to create an
interactive data base.

You will have to have patience for it. I understand that because of data
confidentiality you cant share the complete data but in order to complete
this dashboard or for giving a dashboard skeleton, we would expect one
cleansed workbook only with the necessary sheets.

Hope you understood what I am trying to say.

Thanks,
Anish



On Wed, Jul 20, 2011 at 12:04 PM, Adam Hamilton adamchamil...@gmail.comwrote:

 Hi, Guys,

 Thanks.  You're recieving this error b/c I ommitted the resources
 names for confidentiality.  I'm looking for a macro, or complex
 formulae that will allow the dashboard to be both interactive to the
 user, as well as auto populate data, charts, stats, etc.

 On Tue, Jul 19, 2011 at 6:10 AM, Adam Hamilton adamchamil...@gmail.com
 wrote:
  Hi, guys,
 
  So, I’ve created this complex budget workbook for the department I
  manage.  Everything is automated, however, now that I want to create a
  dashboard with the data, all of my syntax are failing!!!
 
  I’ve spent endless hours on this, can someone PLEASE guide me in the
  right direction?  As you can see from the sample db, the data I want
  to capture are all available in the Raw Data file.  But after weeks of
  trying, I haven’t a clue, and am hard coding everything.
 
  PLEASE HELP
 
 
  --
 
 --
  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


-- 
--
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

Re: $$Excel-Macros$$ I'm in budget vlookup hell. Please help

Hi Adam,

Great thing.
I would request you again to send me only those sheets which you think are
raw data.

Your workbook contains many sheets (includin pivot sheets) which makes me
unsure that which sheet should I consider as a raw data sheet for dashboard.

As you said, your management wants to select a resourse from a list of
resources in order to see their expenses and other details then I assume
there must be a raw data sheet in database format (simply rows and columns).

to protect the confidentiality, you can change the values on your originial
sheet. Replace it with some example values. Change the names, mail address
and amount.

thanks,
Anish
On Wed, Jul 20, 2011 at 12:49 PM, Adam Hamilton adamchamil...@gmail.comwrote:

 Thanks so much.  A dummy dashboard would be great.  Once I can grasp
 the concept, I know it shouldn't be any problem expanding on it at a
 later time.  My idea is for executive mgmt to be able to select a
 resource and see everything pertaining to thier spend and billable
 rates……..

 To answer your question, I was using charts to show my directors my
 bucket spend.  I will be able to omit everything if I can find a way
 to extract everything from the raw data, instead of hundreds of
 vlookups in hidden tabls…..all of the other bucket breakdowns once I
 can get this to make sense from the raw data.

 Attached is a monthly spend ticket as submitted per executive review.

 On Tue, Jul 19, 2011 at 11:44 PM, Anish Shrivastava anish@gmail.com
 wrote:
  Hi Adam,
 
  Looking at your workbook, it seems that it's a complete dashboard project
 to
  be completed and in these cases Sample data wouldn't do the perfect job
  (However we can create one dummy dashboard).
 
  I understand that you want it to be interactive to the user creating
  homepage and navigation with charts and other analysis.
 
  I have one question for you, why are you using images(charts) on the
 sheet.
  where as you can do it with excel charts itself.
 
  Your project is a big one and if we have to do it, it might take some
 time
  to go through all the sheets and understand the data structure to create
 an
  interactive data base.
 
  You will have to have patience for it. I understand that because of data
  confidentiality you cant share the complete data but in order to complete
  this dashboard or for giving a dashboard skeleton, we would expect one
  cleansed workbook only with the necessary sheets.
 
  Hope you understood what I am trying to say.
 
  Thanks,
  Anish
 
 
  On Wed, Jul 20, 2011 at 12:04 PM, Adam Hamilton adamchamil...@gmail.com
 
  wrote:
 
  Hi, Guys,
 
  Thanks.  You're recieving this error b/c I ommitted the resources
  names for confidentiality.  I'm looking for a macro, or complex
  formulae that will allow the dashboard to be both interactive to the
  user, as well as auto populate data, charts, stats, etc.
 
  On Tue, Jul 19, 2011 at 6:10 AM, Adam Hamilton adamchamil...@gmail.com
 
  wrote:
   Hi, guys,
  
   So, I’ve created this complex budget workbook for the department I
   manage.  Everything is automated, however, now that I want to create a
   dashboard with the data, all of my syntax are failing!!!
  
   I’ve spent endless hours on this, can someone PLEASE guide me in the
   right direction?  As you can see from the sample db, the data I want
   to capture are all available in the Raw Data file.  But after weeks of
   trying, I haven’t a clue, and am hard coding everything.
  
   PLEASE HELP
  
  
   --
  
  
 --
   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
 
  --
 
 --
  Some important links for excel users:
  1. Follow us on TWITTER for tips tricks and links

Re: $$Excel-Macros$$ I'm in budget vlookup hell. Please help

Tracking sheet.. I see.. I will have a look and try my best to give one
dummy dashboard by tomorrow.

On Wed, Jul 20, 2011 at 1:16 PM, Adam Hamilton adamchamil...@gmail.comwrote:

 However, I get your point, the data is not raw if I am adding a sum of
 spend based on resource allocation and budgetary spend.

 The 'time tracking' sheet is supposed to be the 'raw' data, but since
 it has been altered, I guess I have none..

 On Wed, Jul 20, 2011 at 12:44 AM, Adam Hamilton adamchamil...@gmail.com
 wrote:
  The 'time tracking' sheet is where all of the data are pulled to
  populate the erroneous amount of fluff that you see in my xls
 
  On Wed, Jul 20, 2011 at 12:35 AM, Anish Shrivastava anish@gmail.com
 wrote:
  Hi Adam,
 
  Great thing.
  I would request you again to send me only those sheets which you think
 are
  raw data.
 
  Your workbook contains many sheets (includin pivot sheets) which makes
 me
  unsure that which sheet should I consider as a raw data sheet for
 dashboard.
 
  As you said, your management wants to select a resourse from a list of
  resources in order to see their expenses and other details then I assume
  there must be a raw data sheet in database format (simply rows and
 columns).
 
  to protect the confidentiality, you can change the values on your
 originial
  sheet. Replace it with some example values. Change the names, mail
 address
  and amount.
  thanks,
  Anish
  On Wed, Jul 20, 2011 at 12:49 PM, Adam Hamilton 
 adamchamil...@gmail.com
  wrote:
 
  Thanks so much.  A dummy dashboard would be great.  Once I can grasp
  the concept, I know it shouldn't be any problem expanding on it at a
  later time.  My idea is for executive mgmt to be able to select a
  resource and see everything pertaining to thier spend and billable
  rates……..
 
  To answer your question, I was using charts to show my directors my
  bucket spend.  I will be able to omit everything if I can find a way
  to extract everything from the raw data, instead of hundreds of
  vlookups in hidden tabls…..all of the other bucket breakdowns once I
  can get this to make sense from the raw data.
 
  Attached is a monthly spend ticket as submitted per executive review.
 
  On Tue, Jul 19, 2011 at 11:44 PM, Anish Shrivastava 
 anish@gmail.com
  wrote:
   Hi Adam,
  
   Looking at your workbook, it seems that it's a complete dashboard
   project to
   be completed and in these cases Sample data wouldn't do the perfect
 job
   (However we can create one dummy dashboard).
  
   I understand that you want it to be interactive to the user creating
   homepage and navigation with charts and other analysis.
  
   I have one question for you, why are you using images(charts) on the
   sheet.
   where as you can do it with excel charts itself.
  
   Your project is a big one and if we have to do it, it might take some
   time
   to go through all the sheets and understand the data structure to
 create
   an
   interactive data base.
  
   You will have to have patience for it. I understand that because of
 data
   confidentiality you cant share the complete data but in order to
   complete
   this dashboard or for giving a dashboard skeleton, we would expect
 one
   cleansed workbook only with the necessary sheets.
  
   Hope you understood what I am trying to say.
  
   Thanks,
   Anish
  
  
   On Wed, Jul 20, 2011 at 12:04 PM, Adam Hamilton
   adamchamil...@gmail.com
   wrote:
  
   Hi, Guys,
  
   Thanks.  You're recieving this error b/c I ommitted the resources
   names for confidentiality.  I'm looking for a macro, or complex
   formulae that will allow the dashboard to be both interactive to the
   user, as well as auto populate data, charts, stats, etc.
  
   On Tue, Jul 19, 2011 at 6:10 AM, Adam Hamilton
   adamchamil...@gmail.com
   wrote:
Hi, guys,
   
So, I’ve created this complex budget workbook for the department I
manage.  Everything is automated, however, now that I want to
 create
a
dashboard with the data, all of my syntax are failing!!!
   
I’ve spent endless hours on this, can someone PLEASE guide me in
 the
right direction?  As you can see from the sample db, the data I
 want
to capture are all available in the Raw Data file.  But after
 weeks
of
trying, I haven’t a clue, and am hard coding everything.
   
PLEASE HELP
   
   
--
   
   
   
 --
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

Re: $$Excel-Macros$$ adding adjacent (contagious) cells only

Hi Azeema,

Here comes the macro for this. (see the attached file)

*Hey Dave,*

Have seen your post after a really long time. :)
Cheers

On Wed, Jul 20, 2011 at 6:16 PM, Dave Bonallack
davebonall...@hotmail.comwrote:

  Hi Azeema,
 Have a look at the attached to see if it meets your needs. The formula is
 really cumbersome, but it's all I can think of at the moment.
 Regards - Dave.

  --
 From: aze...@gmail.com
 Date: Tue, 19 Jul 2011 23:44:14 -1000
 Subject: $$Excel-Macros$$ adding adjacent (contagious) cells only
 To: excel-macros@googlegroups.com


 Dear Experts:
 Let me see if I can explain this to you.

 I am working on a research project which relates to time use from 6am to 12
 pm, by different activities. (see attached data for a selected no of
 respondents for Unpaid Work only). I would like to calculate segments of
 time or unbroken chain of time spent on an activity. For example, if
 someone reported yes (for unpaid work) at 7 am then another yes at 12 pm and
 then at 3 pm, that is obviously 3 segments of time reported for Unpaid work.
 But if someone reports unpaid work at 7 am and it goes on till 10 am it is
 the same number of hours but only 1 segment! While I can calculate the
 amount of total time a person has spent on an activity, I cannot come up
 with the formula to calculate the number of segments for each row
 (respondent). Can someone help me count the SEGEMENTS of time?


 Many thanks in advance.

 Azeema

 --

 --
 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


-- 
--
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


UnpaidWork.xlsm
Description: Binary data

Re: $$Excel-Macros$$ Workbooks Consolidation Macro

One sample sheet would be helpful to us to see the data structure.

However, for this *Also identifiy if there is any row duplicated and
highlight them and paste them into another sheet * There must be any
particular data field which has to be checked against others in order to
find duplicates.

Macro for consolidation is not a big deal.


On Wed, Jul 20, 2011 at 7:35 PM, Harish Sharma harishsharma...@gmail.comwrote:

 Hi,

 I had a macro file which was able to consolidate small files but now I have
 a requirement to consolidate 30 workbooks saved in a share drive and this
 data may even cross 66000 row after consolidation. Can someone help me in
 creating a macro which picks all the file from shared folder and
 consolidates the one specific worksheet in all the workbook.

 This macro should also be capable of deleting any blank row in any of these
 30 workbooks so that we donot miss any data if someone by error has missed a
 couple of row and entered the data in couple of row below.

 30 workbooks in sharedrive
 one specific worksheet from all of these named as Tracker
 Should cover all the blanks and delete them so that no data missed
 Also identifiy if there is any row duplicated and highlight them and paste
 them into another sheet
 If data crosses excel 2003 limit of row it should move to next worksheet.

 Hope to get a solution from excel experts.

 --
 Kind Regards,
 Harish Sharma

 --

 --
 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

Re: $$Excel-Macros$$ Re: Fwd: Need Formula or Macros

Hi Anwar,

Please run the below code and see if it works fine..

--

Sub conc_test()
Dim main_word As String
'cell_pointer = 0
lastrow = Sheets(1).Range(A2).End(xlDown).Row
counter = 3
While counter = lastrow
Set present_cell = Range(E  counter)
Set present_cell1 = Range(D  counter)
word_suffix = 
main_word = Range(D  present_cell.Row)  Range(C  present_cell.Row) 
Range(G  present_cell.Row)
main_word1 = Range(D  present_cell1.Row)  Range(C  present_cell1.Row)
 Range(G  present_cell1.Row)
cell_pointer = 0
While present_cell = present_cell.Offset(cell_pointer + 1, 0)
word_suffix = word_suffix  present_cell.Offset(cell_pointer + 1, 2)
cell_pointer = cell_pointer + 1
'counter = counter + cell_pointer
counter = counter + 1
While present_cell1 = present_cell1.Offset(cell_pointer, 0)
word_suffix = word_suffix  present_cell1.Offset(cell_pointer + 1, 3)
cell_pointer = cell_pointer + 1
'counter = counter + cell_pointer
counter = counter + 1
Wend
Wend
counter = counter + 1
Range(H  present_cell.Row) = main_word  word_suffix
Range(H  present_cell1.Row) = main_word1  word_suffix
Wend

End Sub



On Wed, Jul 20, 2011 at 8:16 PM, cma.ankur ankurpande...@gmail.com wrote:

 hi anwar,
 plz clarify ur query

 On Jul 19, 10:56 pm, john jwa...@gmail.com wrote:
  I sorta see from the notes below the data what you are trying to do,
  but the sample results do not seem to match the data.  ???  please
  check it for correctness or explain further how you reached those
  results
 
  On Jul 19, 5:26 am, Anwar Mohd anr7no...@gmail.com wrote:
 
 
 
 
 
 
 
   Hi All,
 
   Can any one help me with this.
 
   Regards
 
   Anr
 
   -- Forwarded message --
   From: Anwar Mohd anr7no...@gmail.com
   Date: Mon, Jul 18, 2011 at 3:58 PM
   Subject: Need Formula or Macros
   To: excel-macros@googlegroups.com
 
   Hi Experts,
 
   I need formula or macro to concatenete the rows by mail id or reps.
 Attached
   the sample file.
 
   Thanks in advance.
 
   Regards
 
   Anr
 
Concatenate the rows based on the mail ID or reps.xlsx
   13KViewDownload

 --

 --
 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

Re: $$Excel-Macros$$ Macro needed: write cell reference to end of cell, for all cells

Sam,

This is really great to know you got it working tht's the best thing..

The code for find and replace can be reduced to few lines.. but it's fine as
long as it serves the purpose. :)

Cheers,
Anish

On Tue, Jul 19, 2011 at 5:19 PM, Samuel Murray (Afrikaans translator) 
afrika...@gmail.com wrote:

 Anish Shrivastava wrote:

  Anyways, I will give you another macro which will search in each cell on
 the sheet and if it finds a value then it will populate cells address at the
 begining for the text but this may lead to problem with formulas.

 Your latest macro actually stops and gives me a type mismatch error
 when it encounters a formula.

 However, I don't care about formulas as long as the formulas remain
 intact (even if commented out somehow), so I was able to make your
 macro work by first doing a find/replace that replaces = with '=.

 So the revised macro is this:

 Sub test1()
 Dim cel As Range
Cells.Replace What:='=, Replacement:=''=, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
 _
ReplaceFormat:=False
Cells.Replace What:==, Replacement:='=, LookAt:=xlPart,
 SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
 ReplaceFormat:=False
 For Each cel In ActiveSheet.UsedRange
 If cel.Value   Then
 cel.Value = [[[  ActiveSheet.Name  |  Split(cel.Address, $)
 (1)  |  cel.Row  ]]]  cel.Value
 End If
 Next cel
 End Sub

 Sorry, I don't know how to write proper code, so the find/replace was
 just recorded.

 This actually works for my purposes: the cell references appear in the
 sharedStrings.xml of the unzipped XLSX file and can be manipulated
 from there using plaintext editing.

 Thanks again
  Samuel


 --

 --
 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

Re: $$Excel-Macros$$ Need help on String Matching

Hey Haseeb,

This formula is AWESOME man... Tooo goood.. helped me big time..

A big THANK YOU :)

Thanks,
Anish

On Tue, Jul 12, 2011 at 8:25 AM, Haseeb Avarakkan
haseeb.avarak...@gmail.com wrote:

Hello, Try this Array Formula. Must hit CONTROL+SHIFT+ENTER, rather than
ENTER.

=INDEX(Sheet1!A:A,MAX(IFERROR(MATCH(LEFT(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1*,Sheet1!A:A,0),0)))

Copy down. See the attached.

HTH
Haseeb

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

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

Re: $$Excel-Macros$$ Fwd: Need Formula or Macros

Hi Anwar,

You requirement is not clear to me.. Merge few cells (manually) in the
sample sheet according to your requirement and send it.
Will write the code for you.

Thanks,
Anish
On Tue, Jul 19, 2011 at 3:56 PM, Anwar Mohd anr7no...@gmail.com wrote:

 Hi All,

 Can any one help me with this.

 Regards

 Anr

 -- Forwarded message --
 From: Anwar Mohd anr7no...@gmail.com
 Date: Mon, Jul 18, 2011 at 3:58 PM
 Subject: Need Formula or Macros
 To: excel-macros@googlegroups.com


 Hi Experts,

 I need formula or macro to concatenete the rows by mail id or reps.
 Attached the sample file.

 Thanks in advance.

 Regards

 Anr

 --

 --
 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

Re: $$Excel-Macros$$ I'm in budget vlookup hell. Please help

Hi Adam,

Are we missing the Sample db as attachement?

On Tue, Jul 19, 2011 at 6:40 PM, Adam Hamilton adamchamil...@gmail.comwrote:

 Hi, guys,

 So, I’ve created this complex budget workbook for the department I
 manage.  Everything is automated, however, now that I want to create a
 dashboard with the data, all of my syntax are failing!!!

 I’ve spent endless hours on this, can someone PLEASE guide me in the
 right direction?  As you can see from the sample db, the data I want
 to capture are all available in the Raw Data file.  But after weeks of
 trying, I haven’t a clue, and am hard coding everything.

 PLEASE HELP


 --

 --
 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

Re: $$Excel-Macros$$ Macro needed: write cell reference to end of cell, for all cells

2011-07-18 Thread Anish Shrivastava

Samuel,

It was my assumption that your sheet would be the same as my example sheet
which I used for macro. However, I feel sorry that it didnt help you.

Anyways, I will give you another macro which will search in each cell on the
sheet and if it finds a value then it will populate cells address at the
begining for the text but this may lead to problem with formulas. No matter
you put the additional text in the begining or at the end of the cell which
contains any formula. Additional text will break the formula.

Also please note that when we insert the cell's address within it , it would
be the actual address. Suppose if there is any value TEST in cell B16 on
sheet1, the macro would make it [[[Sheet1|B|16]]]TEST. Let me know if this
result is fine with you.

Let me know if it sounds fine with you, for now you can try the below code.
It will work with all the cells no matter where your text is.

*Sub test1()*
*Dim cel As Range*
*For Each cel In ActiveSheet.UsedRange
If cel.Value   Then
cel.Value = [[[  ActiveSheet.Name  |  Split(cel.Address, $)(1) 
|  cel.Row  ]]]  cel.Value
End If
Next cel
End Sub*
**
*Note :-  I can understand completely that you cannot share the sample sheet
with me ;)*

Cheers!!
Anish
On Sun, Jul 17, 2011 at 6:38 PM, Samuel Murray (Afrikaans translator) 
afrika...@gmail.com wrote:

 Anish Shrivastava wrote:

  Please have a look on the attached file. This might be helpful.
 
  I tried it only on one column since I dont know in how many columns you
 will have the data. If you could attach a sample sheet for your data, I can
 make it more feasible.

 Thanks, Anish, for your answer.  My apologies for not responding
 sooner.  I tried your macro on the day that you sent it, but it had a
 number of problems (and I also discovered some other things with
 regard to my needs).

 I thought that it would be okay to add the cell reference to the end
 of cells, but that actually caused certain formula cells to break.  It
 would be better to place the cell reference at the start of the cell.

 Your macro stops when it reaches an empty cell (even if that is not
 the last cell).

 From what I could see from your macro, the macro does not actually
 query a cell to find out what its position is, but just assumes that
 the first cell is in row 1, the second cell is in row 2, etc.  I
 wonder if this procedure might break if some cells are merged (for
 then the third cell may be in row 4 already, if cell 2 and 3 are
 merged).

 I tried to get your macro to work with both rows and columns by
 nesting two loops, but I'm no expert at Excel macros and I could not
 get the syntax to work.  Bummer. :-)  Though even if I did get it
 working, I suspect that the macro would not work because of the issue
 with merged cells.

 My spreadsheets typically have tens or hundreds of rows and columns,
 and some of the cells may be merged, so any solution would have to
 take that into account.  Unfortunately I can't send you an example
 file owing to confidentiality reasons... but any Excel file of e.g. a
 financial report should be a good example.

 Possible alternative approach

 An alternative approach (I'm not sure if this would have any bearing
 on how easy it would be to write a macro for it) would be that I add a
 nonsense string in front of each cell, and that the macro replaces
 that with the cell reference.  Adding e.g. XYZXYZ to the front of
 each cell would be simple (just do find/replace on the XML in the XLSX
 file).

 So, if you had a worksheet in which each cell contains XYZXYZ
 somewhere in the cell (or at the start of the cell), would it be
 possible to do a type of find/replace that replaces the XYZXYZ with
 the cell's reference (e.g. in the format [SheetName|Row|Col])?

 It would have to be the cell's actual reference (i.e. the cell must be
 queried to find out its position), and not just an assumed reference
 based on the number of rows or columns from the A1 position.

 Or would that have no effect on how easy/difficult it would be to
 write this macro?

 Thanks
 Samuel


 --

 --
 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

Re: $$Excel-Macros$$ Merge Vertically

Hi Karan/Norain,

what is use of merging all the cells on a sheet. After merging what are you
going to do with the sheet as it will become like a Notepad page and you
cant use any other formula/function.

I am asking this just out of my curiousity, would appreciate your answer.

Thanks,
Anish

On Fri, Jul 15, 2011 at 3:43 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote:

 Sub mergeall()

 Worksheets(Sheet4).Rows.Select

 With Selection

 .HorizontalAlignment = xlCenter

 .VerticalAlignment = xlBottom

 End With

 Selection.Merge

 End Sub


 On Fri, Jul 15, 2011 at 3:03 PM, karan 1237 karan1...@gmail.com wrote:

  *
 Hey friend,

 I facing this problem. I want to merge all columns in one time.

 Kαяαη*



  On Fri, Jul 15, 2011 at 2:56 PM, NOORAIN ANSARI 
 noorain.ans...@gmail.com wrote:

 Dear Karan,

 Please try it

 Sub mergecell()
 With Selection
 .HorizontalAlignment = xlGeneral
 .MergeCells = True
 End With
 End Sub

   On Fri, Jul 15, 2011 at 12:56 PM, karan 1237 karan1...@gmail.comwrote:

 Hello Friends,

 Once again I need your help. There is option for merge across in excel
 but is there any macro to merge vertically. It will merge selected range in
 vertically.


 Thnx
 Karan




 --

 --
 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




 --
 Thanks  regards,
 Noorain Ansari
 *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.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




 --
 Thanks  regards,
 Noorain Ansari
 *http://noorain-ansari.blogspot.com/*http://noorain-ansari.blogspot.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

Re: $$Excel-Macros$$ RE: Want to copy two rows in all sheets except few

Hello Sir,

I guess, I answered this query yesterday with attachment.

Please let me know if it didnt work or you want a different solution.

This was the code..

Sub copy_rows()
Dim sht As Integer

  For sht = 5 To Worksheets.Count
   Sheets(4).Select
 Rows(5:6).Copy
  Sheets(sht).Select
   Rows(5).Select
ActiveSheet.Paste
  Next sht
End Sub

thanks


On Fri, Jul 15, 2011 at 1:25 PM, MITTAL AJAY amit...@iocl.co.in wrote:

  Dear All,

 ** **

 I want to copy  paste special rows 5 6 of sheet “Heading” to all the
 sheets starting from “Balance Sheet” to “Note 31”

 ** **

 Can someone suggest code for the same.

 ** **

 *शुभकामनाओं सहित,*

 * *

 *अजय मित्तल,***

 *वित्त प्रबन्धक ***

 *पाइपलाइंस मुख्यालय *

 *टेलीफोन:0120-2448312*

 *ई-मेल** :** **amit...@iocl.co.in**  ***

 ** **

 *This Message was sent from Indian Oil Messaging Gateway, Refineries HQ,
 New Delhi, India. The information contained in this electronic message and
 any attachments to this message are intended for the exclusive use of the
 addressee(s) and may contain proprietary, confidential or privileged
 information. If you are not the intended recipient, you should not
 disseminate, distribute or copy this e-mail. Please notify the sender
 immediately and destroy all copies of this message and any attachments.*

 --

 --
 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

Re: $$Excel-Macros$$ Multiple excel files

Hi Shrinivas,

Attached please find the Dump file with macro. You will have to copy and
paste your unique account numbers in Cell *M1 *before running the macro.

Data will be splitted based on the account numbers and new files will be
saved with account names.

Also note that I have done it with Excel 2007, if you dont have this
version, please let me know so that I can make it for 2003.

Let me know if you face any issues with this macro.

Below is the code for your ref.

-
Sub split_data()
Dim lst_row, lst_col, i, j As Integer
Dim ws_name As String
Dim nwkb As Workbook
Dim ac_num As String
Dim uname As String

Sheets(1).Rows(1).Select
Selection.Insert shift:=xlDown

lst_row = Sheets(1).Range(A2).End(xlDown).Row
lst_col = Sheets(1).Range(A3).End(xlToRight).Column
Range(A1, Cells(1, lst_col)).Select
Selection.AutoFilter

For j = 2 To Sheets(1).Range(M2).End(xlDown).Row
ac_num = Sheets(1).Cells(j, M).Value
uname = Environ(Username)
ActiveSheet.Range(A1, Cells(lst_row, lst_col)).AutoFilter
Field:=1, Criteria1:=ac_num

For i = 2 To 1000 ' change to your ranges
If Not Rows(i).Hidden Then
theFirstUnhiddenRowinArea = i
Range(B  i).Select
Exit For
End If
Next
Range(Selection, Cells(lst_row, lst_col)).Select

Selection.Copy
Set nwkb = Workbooks.Add
nwkb.Activate
ActiveSheet.Paste
nwkb.SaveAs C:\Users\  uname  \Desktop\  ac_num  .xlsx,
FileFormat:=xlOpenXMLWorkbook
nwkb.Close
Next j
Sheets(1).Rows(1).Delete shift:=xlUp
End Sub
--

Thanks,
Anish

On Fri, Jul 15, 2011 at 4:12 PM, Chidurala, Shrinivas 
shrinivas.chidur...@citi.com wrote:

  Dear Friends,

 Please help me create macro for splitting the dump file into multiple
 excel files as per account number in *column A* and file name should be
 account no. I have using the attached code for splitting the different dump
 file but it is not working in this dump file. Please advise on the same.

 Find attached sample of dump file , required format and code.


 ***Dump file   R**equired **file*

 Thanking in advance.

 Regards,
 Shrinivas




 --

 --
 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


Dump_Report_Anish.xlsm
Description: Binary data

Re: $$Excel-Macros$$ Multiple excel files