RunTime Error 1004 for Copy to New Workbook

[postelrich]

Hi, I have one master file where I need to split out and send data 15 different regions. I've tried make a macro to automate it by using autofilter and copy to achieve it but I keep getting a Run time Error 1004. I am using Excel 2007, my code below though I cut out the values for the string array. If I step into the code, the error pops up when I get to the copy line.

Sub SplitFile()
Dim regions(15) As String
regions(0) = "FLORIDA"

For N = LBound(regions) To UBound(regions)
    Set wb = Workbooks.Add
       
    With ThisWorkbook.Sheets("MASTER")
        .Activate
        .AutoFilterMode = False
        .Range("C:BY").AutoFilter
        .Range("C:BY").AutoFilter Field:=3, Criteria:=regions(N)
        .Range("C:BY").SpecialCells(xlCellTypeVisible).Select
        .Range("C:BY").Copy
    End With
    
    With wb
        .Sheet1.Paste
        .SaveAs Filename:="C:\Regional Files\" & regions(N)
        .Close True
    End With
    
    Set wb = Nothing
Next N
End Sub

 

[postelrich]

eh?

 

[Norie]

Why not split the data out into separate worksheets in the current workbook and then copy those individual worksheets to new workbooks?

As for the error, perhaps that could be because you are looping through an array where only 1 item has a value, eg FLORIDA, and the others are empty.

 

[postelrich]

Well the point is to avoid having to do it manually. I could copy the information to the new workbooks just as easily as I could copy it to a new worksheet. Unless you meant to do it through vba of course.

I should state that the error occurs when it tries to filter by criteria.

The rest of the array does have value, just left it out for posting here.

 

[Norie]

Who said anything about doing anything manually?

The whole thing can be done in code.

 

[postelrich]

hmmm my basic understanding of vba would probably lead to similar errors if i try to go the other way. I found the 1004 error to be caused by not including a 1 in criteria:= . Now i get run time error 438 now when it tries to paste

 

[Norie]

To be honest I can't help you with that error or the original code.

However I might be able to post code that will split out the data to separate worksheets and create new workbooks from them

I'd need some info about the data and how you are splitting it out.

For example:

What range is the data in, does it have headers?

Do you want to split it out by based on a particular column/header?

Is the data to be split based on the unique values in that column?