Hey Daniel, There are around 11 no. of enterprises that own a particular/ individual sheet. for this do I need to create a different module for all the 11 no. of enterprises? As far as the"others" sheet is concerned it would contain around more than 80-90 enterprises, other than these 11 enterprises for which the particular/individual sheet is prepared. In the example that we have considered; Company A, Company B, Company C, Company D are the enterprises which need to be included in the "other" sheet. Also please tell me how do I upload an excel file, so that I can be clearer on what exactly I am asking for. Thanks. Regards, Suhas.
On Feb 21, 9:56 pm, "Daniel" <dcolarde...@free.fr> wrote: > Hello, > I have to know which enterprise owns a particular sheet and which one is > grouped in the "others" sheet. > Maybe one more column in the "Main" sheet ? > Regards. > Daniel > > -----Message d'origine----- > De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De > la part de Suhas > Envoyé : lundi 21 février 2011 07:33 > À : MS EXCEL AND VBA MACROS > Objet : Re: $$Excel-Macros$$ I need to extract data from a sheet to another > in the same workbook - please help. > > HI Daniel, > Thanks a million. This works absolutely fine!! > Now my doubt is do i need to create a different module for all the clients > for their respective individual sheets? > Also is it possible to have the data of Company A, Company B, Company C etc. > all in one sheet named "Others"? > If at all I could upload the excel file, i would be in a better position to > explain my queries to you. > Again Thanks a lot..! > Regards, > Suhas. > > On Feb 20, 4:05 pm, "Daniel" <dcolarde...@free.fr> wrote: > > > > > Hello, > > To create the first ABC sheet from "Main" sheet , paste the following > > macro in a standard module : > > > Sub test() > > Dim inCalculationMode As Integer > > Dim rng As Range, sh As Worksheet > > Dim teste As Boolean > > Application.ScreenUpdating = False > > inCalculationMode = Application.Calculation > > Application.Calculation = xlCalculationManual > > With Sheets("Main") > > Set rng = .Range(.[A1], .[F65536].End(xlUp)) > > End With > > rng.AutoFilter > > rng.AutoFilter Field:=3, Criteria1:="ABC Enterprises" > > For Each sh In Sheets > > If sh.Name = "ABC Enterprises" Then > > Sheets("ABC Enterprises").Select > > Cells.ClearContents > > teste = True > > Exit For > > End If > > Next sh > > If teste = False Then > > Sheets.Add > > ActiveSheet.Name = "ABC Enterprises" > > End If > > rng.SpecialCells(xlCellTypeVisible).Copy [A1] > > rng.AutoFilter > > Application.Calculation = inCalculationMode > > Application.ScreenUpdating = True > > End Sub > > > To automatically update the ABC sheet, paste the following macro in > > the "Main" sheet module : > > > Private Sub Worksheet_Change(ByVal Target As Range) > > Dim c As Range > > If Intersect(Target, [A:F]) Is Nothing Then Exit Sub > > For Each c In Target > > If Application.CountA(Range("A" & c.Row & ":F" & c.Row)) = 6 > > Then > > If Cells(c.Row, 3) = "ABC Enterprises" Then > > test > > End If > > End If > > Next c > > End Sub > > > You can see the sample file here :http://www.filedropper.com/110221 > > Regards. > > Daniel > > > -----Message d'origine----- > > De : excel-macros@googlegroups.com > > [mailto:excel-macros@googlegroups.com] De la part de Suhas Envoyé : > > dimanche 20 février 2011 07:31 À : MS EXCEL AND VBA MACROS Objet : Re: > > $$Excel-Macros$$ I need to extract data from a sheet to another in the > > same workbook - please help. > > > hi Daniel.. > > I am not able to attach any file with the post, i mean i am not having > > any option to attach file; if there's any way please let me know, this > > is the first time I am posting. > > Below is the sample data just pasted from excel. > > > now this is the main sheet where in I will be entering the data, and I > > want this data to be reflected automatically in their individual > > sheets/workbooks (different workbook can be created all together) > > > Sr. No. Invoice No. Name Amount Tax Total Amount > > 1 101/001 ABC Enterprises 2550.00 319.00 2869.00 > > 2 101/002 XYZ Corp. 3500.00 438.00 3938.00 > > 3 101/003 PQR Co. 12250.00 1531.00 13781.00 > > 4 101/004 MNO Services 10000.00 1250.00 11250.00 > > 5 101/005 ABC Enterprises 5220.00 653.00 5873.00 > > 6 101/006 MNO Services 4585.00 573.00 5158.00 > > 7 101/007 PQR Co. 6550.00 819.00 7369.00 > > 8 101/008 XYZ Corp. 1000.00 125.00 1125.00 > > 9 101/009 XYZ Corp. 7450.00 931.00 8381.00 > > 10 101/010 Company A 5450.00 681.00 6131.00 > > 11 101/011 Company B 6550.00 819.00 7369.00 > > 12 101/012 Company C 4450.00 556.00 5006.00 > > 13 101/013 Company D 5000.00 625.00 5625.00 > > 14 101/014 ABC Enterprises 7200.00 900.00 8100.00 > > 15 101/015 MNO Services 8200.00 1025.00 9225.00 > > > Here, I need to make a new worksheet/workbook for ABC Enterprises, as > > and when the above sheet is updated, automatically the corresponding > > row with the data of ABC Enterprises should be extracted from the main > > worksheet and updated to the respective individual worksheet/workbook. > > I tried doing the same by using MATCH, VLOOKUP, but in vain. Is there > > any other formula or I'll require a VB code for that, please help. > > Thank you. > > Regards, > > Suhas. > > > On Feb 19, 4:08 pm, "Daniel" <dcolarde...@free.fr> wrote: > > > > Hello, > > > An empty sample file, with only the headers would help. If it's not > > > possible, please indicate which are the headers and their respective > > > columns. > > > Regards. > > > Daniel > > > > -----Message d'origine----- > > > De : excel-macros@googlegroups.com > > > [mailto:excel-macros@googlegroups.com] De la part de Suhas Envoyé : > > > samedi 19 février 2011 10:21 À : MS EXCEL AND VBA MACROS Objet : > > > $$Excel-Macros$$ I need to extract data from a sheet to another in > > > the same workbook - please help. > > > > Hi. > > > I am currently recording my accounts in Excel. The main accounts > > > sheet contains the data of - customers - invoices - date and amount. > > > Now According to the customers, I need to maintain the individual > > > accounts of these customers in their corresponding/respective sheets > > > in the same/different workbook. > > > So I need some formula/code so that the respective individual sheet/ > > > workbook of the clients should be updated automatically as and when > > > the main accounts sheet is updated. > > > Please Help. > > > Thank you. > > > Suhas > > > > P. S.: I am not able to upload the sample file. > > > > -- > > > -------------------------------------------------------------------- > > > -- > > > ------ > > > ------ > > > Some important links for excel users: > > > 1. Follow us on TWITTER for tips tricks and links > > > :http://twitter.com/exceldailytip2. Join our LinkedIN group > > > @http://www.linkedin.com/groups?gid=1871310 > > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com > > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com5. Excel Tips > > > and Tricks athttp://exceldailytip.blogspot.com > > > > To post to this group, send email to excel-macros@googlegroups.com > > > > <><><><><><><><><><><><><><><><><><><><><><> > > > Like our page on facebook , Just follow below > > > linkhttp://www.facebook.com/discussexcel > > > -- > > ---------------------------------------------------------------------- > > ------ > > ------ > > Some important links for excel users: > > 1. Follow us on TWITTER for tips tricks and links > > :http://twitter.com/exceldailytip2. Join our LinkedIN group > > @http://www.linkedin.com/groups?gid=1871310 > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com5. Excel Tips > > and Tricks athttp://exceldailytip.blogspot.com > > > To post to this group, send email to excel-macros@googlegroups.com > > > <><><><><><><><><><><><><><><><><><><><><><> > > Like our page on facebook , Just follow below > > linkhttp://www.facebook.com/discussexcel > > -- > ---------------------------------------------------------------------------- > ------ > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links > :http://twitter.com/exceldailytip2. Join our LinkedIN group > @http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials athttp://www.excel-macros.blogspot.com > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com5. Excel Tips and > Tricks athttp://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below > linkhttp://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
