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
