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