CODEWITHSUNDEEP
vbaexcelexcel-2010
user3067028
user3067028

Reputation: 159

Add To Existing Macro

I see similar questions on the forum about adding to macros, but none of them solved the issue for me.

Right now when I run the macro below, it creates a new workbook with only one sheet in it. I need it to create 3 sheets in the new workbook instead of 1. Please help

Sub RunSupplierOTD()
'
' RunSupplierOTD Macro
'

'
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    Range("H49").Select
    ActiveCell.FormulaR1C1 = "Vendor Name"
    Range("I49").Select
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    Range("A49:S49").Select
    Range("S49").Activate
    Selection.AutoFilter
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveSheet.Range("$A$49:$S$177").AutoFilter Field:=7, Criteria1:=Array("#" _
        , "12633", "79204", "79247", "79371", "79479", "79498", "79583", "IC3000"), Operator _
        :=xlFilterValues

End Sub

Upvotes: 1

Views: 1054

Answers (1)

user844705
user844705

Reputation:

One answer to the OPs question of how to add a sheet to a workbook in Excel VBA is to use the following code:

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

One thing I would say is that recording macros in Excel produces loads and loads of unnecessary code. One thing that it teaches you though is how to do certain things. In the comments of the question the OP asks a very common question for people beginning in Excel VBA, namely how to select objects such as sheets and cells. There are many ways to do this, each using different methods such as "Application.Goto".

A good reference can be found at the link below and I would recommend anyone looking to get started in Excel VBA to read this link

http://support2.microsoft.com/kb/291308

as well as recording macros and examining the resulting code.

Hth, O

Upvotes: 1

Related Questions