Haven’t followed this so
Send your file with a complete explanation and before/after examples to
dguille...@gmail.com
From: Mr excel
Sent: Thursday, September 29, 2011 4:40 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Create worksheet from list
Macro 1:
Option Explicit
Sub ExtractReps()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("Sheet1")
Set rng = Range("Database")
'extract a list of Sales Reps
ws1.Columns("C:C").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
r = Cells(Rows.Count, "J").End(xlUp).Row
'set up Criteria Area
Range("L1").Value = Range("C1").Value
For Each c In Range("J2:J" & r)
'add the rep name to the criteria area
ws1.Range("L2").Value = c.Value
'add new sheet and run advanced filter
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
Next
ws1.Select
ws1.Columns("J:L").Delete
End Sub
Macro 2:
Option Explicit
Sub MakeRepSheetsSAS()
Dim c As Range
Dim lr As Long
Dim slr As Long
Application.ScreenUpdating = False
With Sheets("sheet1")
.Columns("C:C").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
lr = .Cells(Rows.Count, 1).End(xlUp).Row
slr = .Cells(Rows.Count, "j").End(xlUp).Row
For Each c In Range("j2:j" & slr)
.Range("A1:G1").AutoFilter
Sheets.Add After:=Sheets(Sheets.Count)
.Range("A1:G" & lr).AutoFilter Field:=3, Criteria1:=c
.Columns("a:g").Copy Range("a1")
ActiveSheet.Name = c
'.Range("A1:G" & lr).AutoFilter
Next c
.Range("A1:G" & lr).AutoFilter
End With
End Sub
These are the macros in the example quoted earlier in this post.My Request is
that, how would i modify the macro to suit my needs....
My requirements:
1.On entering the data in the main sheet.(sheet1) here.Worksheets to be
generated basing on the new reps added.
2.after the new data is added to the sheet, Rows should be updated on entry to
the respective sheets based on reps.
Everything is same but only the macro is run with the data in the main sheet in
the actual scenario.but whereas,in my requirement i need the data to be updated
immediately on entry in the main sheet.
Hope i had explained it better..Thanks in advance for sparing time...
--
----------------------------------------------------------------------------------
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
