Vba code for creating and renaming a new sheet

[CROY1985]

Hi all,

I need some help in tweaking the code below. I have changed the text colour to red for the parts which i believe are the problem.

Sub MSvone()
'
' MSvone Macro
    Sheets("Sheet1").Select
    ActiveSheet.ListObjects("Table_Query_from_Sage_Accounts_2011_1").Range. _
        AutoFilter Field:=1, Criteria1:="=*00", Operator:=xlOr, Criteria2:= _
        "=*all*"
    Cells.Select
    Selection.Copy
    [COLOR=red]Sheets.Add After:=Sheets(Sheets.Count)[/COLOR]
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
[COLOR=red]    Sheets("Sheet7").Select[/COLOR]
[COLOR=red]    Sheets("Sheet7").Name = "Marshall Street"[/COLOR]
    Columns("A:A").Select
    Selection.EntireColumn.Hidden = True
End Sub

The second time the macro is run, the new sheet that is created is sheet8. So the part of this code that refers to sheet 7 to rename it, doesnt work.

How can i have the new sheet renamed whichever sheet number excel happens to assign it?

 

[kpark91]

Sub Test3()
    Dim a As Worksheet
    Set a = Sheets.Add(After:=Sheets(Sheets.count))
    a.Name = "Hi"
End Sub

 

[CROY1985]

do i replace the first line i have highlighted in red with what you put and delete the other 2 lines?

Thanks

 

[kpark91]

Here is your whole code

Sub MSvone()
'
' MSvone Macro
    Dim newWS As Worksheet
    Sheets("Sheet1").Select
    ActiveSheet.ListObjects("Table_Query_from_Sage_Accounts_2011_1").Range. _
        AutoFilter Field:=1, Criteria1:="=*00", Operator:=xlOr, Criteria2:= _
        "=*all*"
    Cells.Select
    Selection.Copy
    Set newWS = Sheets.Add(After:=Sheets(Sheets.Count))
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    newWS.Name = "Marshall Street"
    Columns("A:A").Select
    Selection.EntireColumn.Hidden = True
End Sub

Btw, you can further reduce your code by removing select.

ActiveSheet.Select
Selection.Blarg

to

ActiveSheet.Blarg

It makes the code more readable and faster.

 

[CROY1985]

I have finised with this. I works very well and quickly too. Thank you for your help - it would have took me weeks if i didnt have it.

Sub createmh()
    
    Selection.AutoFilter
       
        ActiveSheet.ListObjects("Table_Query_from_Sage_Accounts_2011_1").Range. _
        AutoFilter Field:=1, Criteria1:="=*00", Operator:=xlOr, Criteria2:= _
        "=*all*"
    Cells.Select
    Selection.Copy
    Dim a As Worksheet
    Set a = Sheets.Add(After:=Sheets(Sheets.Count))
    a.Name = "Marshall Street"
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Columns("A:A").Select
    Selection.EntireColumn.Hidden = True
         
End Sub

 

[kpark91]

Thanks for your feedback.
I'm glad it has helped you.