Open workbook and add new sheet VBA

[HappyLadyToo]

Hi - I have been working in this since yesterday and cannot figure out the last part. The macro works great (Thank you Jerry Beaucaire (4/22/2010)), but I'm having trouble modifying it for the second step.

I have a spreadsheet with all vendors listed with their data. A macro is run and all of the vendors are split into their own workbooks with their own names as the file name. After this split, there is a second filter and that is where the problem is occurring.

When the workbooks are generated, only Sheet 1 is generated so when I run the second macro, there is no Sheet 2 to paste the filtered data from the second macro. I can get the correct workbook opened but then the process stops.

I've tried to add the sheet to either process and can't make it happen. The section where I'm stuck is italicized.

Will someone please look at the code and tell me what I'm doing wrong?

Option Explicit


Sub SplitVendors()
'Jerry Beaucaire  (4/22/2010)
'Based on selected column, data is filtered to individual workbooks
'workbooks are named for the value plus today's date
Dim LR As Long, Itm As Long, MyCount As Long, vCol As Long
Dim ws As Worksheet, MyArr As Variant, vTitles As String, SvPath As String


'Sheet with data in it
   Set ws = Sheets("Sheet1")


'Path to save files into, remember the final \
    SvPath = "C:\Users\u138044\Documents\test files\suppliers\vendor\"


'Range where titles are across top of data, as string, data MUST
'have titles in this row, edit to suit your titles locale
    vTitles = "A1:P1"
   
'Choose column to evaluate from, column A = 1, B = 2, etc.
   vCol = Application.InputBox("What column to split data by? " & vbLf _
        & vbLf & "(A=1, B=2, C=3, etc)", "Which column?", 1, Type:=1)
   If vCol = 0 Then Exit Sub


'Spot bottom row of data
   LR = ws.Cells(ws.Rows.Count, vCol).End(xlUp).Row


'Speed up macro execution
   Application.ScreenUpdating = False


'Get a temporary list of unique values from key column
    ws.Columns(vCol).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Range("EE1"), Unique:=True


'Sort the temporary list
    ws.Columns("EE:EE").Sort Key1:=ws.Range("EE2"), Order1:=xlAscending, Header:=xlYes, _
       OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal


'Put list into an array for looping (values cannot be the result of formulas, must be constants)
    MyArr = Application.WorksheetFunction.Transpose(ws.Range("EE2:EE" & Rows.Count).SpecialCells(xlCellTypeConstants))




    ws.Range("EE:EE").Clear    'clear temporary worksheet list


    ws.Range(vTitles).AutoFilter




    For Itm = 1 To UBound(MyArr)    'Loop through list one value at a time
        ws.Range(vTitles).AutoFilter Field:=vCol, Criteria1:=MyArr(Itm)




[I]        ws.Range("A1:A" & LR).EntireRow.Copy[/I]

[I]            ChDir "C:\Users\u138044\Documents\test files\suppliers\vendor"[/I]

[I]    Workbooks.Open Filename:= _[/I]
[I]        "C:\Users\u138044\Documents\test files\suppliers\vendor\" & MyArr(Itm) & ".xlsx"[/I]

[I]        Worksheets.Add After:=Worksheets("Sheet1")[/I]

[I]        ActiveSheet.PasteSpecial xlPasteAll[/I]
[I]        Cells.Columns.AutoFit[/I]
[I]        Application.CutCopyMode = False[/I]
[I]        ActiveWorkbook.Save[/I]
[I]        ActiveWindow.Close[/I]

[I]        ActiveWorkbook.Close False


[/I]
        
        ws.Range(vTitles).AutoFilter Field:=vCol
    Next Itm


    ws.AutoFilterMode = False
    MsgBox "Rows with data: " & (LR - 1) & vbLf & "Rows copied to other sheets: " & MyCount & vbLf & "Hope they match!!"
    Application.ScreenUpdating = True
End Sub

 

[JLGWhiz]

Worksheets.Add After:=Worksheets("Sheet1")

Assuming you have a Sheets("Sheet1"), this should add the additional worksheet, but you might try adding in a reference to the workbook.

Worksheets.Add After:=ActiveWorkbook.Worksheets("Sheet1")

If you don't have a sheet named "Sheet1" it should give you a "Subscript out of range" error message.
Have you used the F8 function key to step through the code an see if the highlight skips over that portion of the code, and if so, where does the skip occur?

 

[HappyLadyToo]

Thanks, works great! I figured it would be something simple.

 

[JLGWhiz]

Thanks, works great! I figured it would be something simple.

You're welcome.
regards, JLG