excel inserting and re-naming worksheets using a macro

[cgilmore]

I have Windows XP and Office XP. I have a spreadsheet that we download every month and it will have 22,000 plus rows. I then insert 10 new worksheets and name them for each acct. name such as EFC, OVK, etc. Then I use Data, Filter to pull up the account name and copy these sheets to the appropriate worksheet. I tried creating a macro to do this for me and I get errors. When I go to debug it will highlight Sheets and the name of the Sheet such as OVK. What am I doing wrong? Is there a better way of doing this?

 

[SkipVought]

Hi,

WHY???

What's the difference in selecting a TAB or selecting an entry in a DROPDOWN in the only sheet that you ought to have in the workbook with the AutoFilter???

Why go to all that bother. just filter!

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[cgilmore]

They want a worksheet for each account and will sort and apply different formulas for each account.

 

[SkipVought]

So how are these formulas different. Give me several examples.

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[01Patrik]

Your data is 'Sheet1' in code below. If you insert a sheet ('Sheet2') with all the new sheets names in Col A and the thing to filter for in Col B, starting at row 1 (no headers), then something like this should do:

dim n as integer, ShN as string, Cr as string

sheet2.activate

for n = 1 to cells.find(what:="*", searchorder:=xlbyrows, searchdirection:=xlprevious).row

ShN = sheet2.cells(n,1).value
Cr = sheet2.cells(n,2).value

sheets.add after:=sheets.count
activesheet.name = ShN

sheet1.activate
activesheet.autoFilter field:=1, criteria1:=Cr

cells.copy

sheets(ShN).activate
range("A1").pastespecial xlpastevalues

next

Requires an existing autofilter on 'Sheet1', and you need to change which field to apply the filter to.

Cheers

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[cgilmore]

Patrick,

My source data is on sheet1. I typed the following info into Sheet2:
A B
WILDER ACCOUNT
EVANS ACCOUNT
GAMBRELL ACCOUNT

On Sheet1 the field I want to autofilter by is Account. I moved The Account information to column A because your formula has a field:=1. I turn on autofilter before I run the code. I have tried running it with Wilder as the result of the autofilter and without. I get the same error. When I run it I get an error, when I click on debug it highlights the following line:

Sheets.Add after:=Sheets.Count

 

[SkipVought]

Sheets.Add after:=Sheets(Sheets.Count)

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[cgilmore]

Thank you Skip. I changed that line and now it stops at Sheet2.Activate. Can you help?

 

[SkipVought]

    Dim n As Integer, ShN As String, Cr As String
    
    With sheets2
        For n = 1 To .Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
        
            ShN = .Cells(n, 1).Value
            Cr = .Cells(n, 2).Value
            
            Sheets.Add after:=Sheets(Sheets.Count)
            ActiveSheet.Name = ShN
            
            .AutoFilter field:=1, Criteria1:=Cr
            
            .Cells.Copy
            
            Sheets(ShN).Range("A1").PasteSpecial xlPasteValues
        
        Next
    End With

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[cgilmore]

I assumed I needed to give this macro a name so I named it

Sub Add_Worksheets_Copy_Data()

at the top before the Dim command line
When I run the macro it highlights the above Sub line with yellow. It says Compile Error Expected End Sub. I tried changing the End With to End Sub and then I get Compile Error Expected End With.

 

[SkipVought]

Sub YourName()
    Dim n As Integer, ShN As String, Cr As String
    
    With sheets2
        For n = 1 To .Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
        
            ShN = .Cells(n, 1).Value
            Cr = .Cells(n, 2).Value
            
            Sheets.Add after:=Sheets(Sheets.Count)
            ActiveSheet.Name = ShN
            
            .AutoFilter field:=1, Criteria1:=Cr
            
            .Cells.Copy
            
            Sheets(ShN).Range("A1").PasteSpecial xlPasteValues
        
        Next
    End With
End Sub

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[cgilmore]

It says error and It highlights the n=1 command line.

Sub Add_Worksheets_Copy_Data()

Dim n As Integer, ShN As String, Cr As String

With sheets2
For n = 1 To .Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

ShN = .Cells(n, 1).Value
Cr = .Cells(n, 2).Value

sheets.Add after:=sheets(sheets.Count)
ActiveSheet.Name = ShN

.AutoFilter field:=1, Criteria1:=Cr

.Cells.Copy

sheets(ShN).Range("A1").PasteSpecial xlPasteValues

Next
End With

End Sub

 

[SkipVought]

Sorry, Sheet2 not Sheets2 and also the AutoFilter statement...

Sub YourName()
    Dim n As Integer, ShN As String, Cr As String
    
    With Sheet2
        For n = 1 To .Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
        
            ShN = .Cells(n, 1).Value
            Cr = .Cells(n, 2).Value
            
            Sheets.Add after:=Sheets(Sheets.Count)
            ActiveSheet.Name = ShN
            
            .[A1].AutoFilter field:=1, Criteria1:=Cr
            
            .Cells.Copy
            
            Sheets(ShN).Range("A1").PasteSpecial xlPasteValues
        
        Next
    End With
End Sub

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]