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