Macro to create worksheets and transport data from Main list

Hi I already have this Macro in my spreadsheet to create worksheets and transport data from the main spreadsheet. It worked fine when I used it the first time, but now it keeps coming up with this message 'Compile Error: Invalid Outside Procedure' and flagging up 'Application.ScreenUpdating = False' with the False as not recognised.



Dim mySheets
Dim wbNew As Workbook
Dim wsROR As Worksheet
Dim wsPM As Worksheet

Application.ScreenUpdating = False

Set wsROR = Worksheets("ROR")
Set wsPM = Worksheets("PM")
mySheets = wsPM.Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp)).Value
Workbooks.Add
ChDir "C:\Documents and Settings\All Users\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\All Users\Desktop\New.xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
Set wbNew = ActiveWorkbook
For i = 1 To UBound(mySheets)
wsROR.Copy After:=wbNew.Sheets(Sheets.Count)
With ActiveSheet
On Error Resume Next
ActiveSheet.Name = mySheets(i, 1)
Err.Clear
With .UsedRange
.AutoFilter Field:=1, Criteria1:="<>" & mySheets(i, 1)
.Offset(1).EntireRow.Delete
.AutoFilter
End With
End With
Next i
With wbNew
Application.DisplayAlerts = False
.Sheets("Sheet1").Delete
Application.DisplayAlerts = True
.Save
End With
Set wbNew = Nothing
Set wsROR = Nothing
Application.ScreenUpdating = True
End Sub

also is there anyway I can alter this so it doesn't create a new workbook

You seem to be missing an initial line of code which would be something like

Sub MySub()

Sorry, didn't copy that across - should be as below
however it now keeps flagging up this line - 'mySheets = wsPM.Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp)).Value' with 'Range of object '_worksheet' failed

Sub divider()
Dim mySheets
Dim wbNew As Workbook
Dim wsROR As Worksheet
Dim wsPM As Worksheet

Application.ScreenUpdating = False
Set wsROR = Worksheets("ROR")
Set wsPM = Worksheets("PM")
mySheets = wsPM.Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp)).Value
Workbooks.Add
ChDir "C:\Documents and Settings\All Users\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\All Users\Desktop\New.xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
Set wbNew = ActiveWorkbook
For i = 1 To UBound(mySheets)
wsROR.Copy After:=wbNew.Sheets(Sheets.Count)
With ActiveSheet
On Error Resume Next
ActiveSheet.Name = mySheets(i, 1)
Err.Clear
With .UsedRange
.AutoFilter Field:=1, Criteria1:="<>" & mySheets(i, 1)
.Offset(1).EntireRow.Delete
.AutoFilter
End With
End With
Next i
With wbNew
Application.DisplayAlerts = False
.Sheets("Sheet1").Delete
Application.DisplayAlerts = True
.Save
End With
Set wbNew = Nothing
Set wsROR = Nothing
Application.ScreenUpdating = True
End Sub

You need to fully qualify the range

Please Login or Register  to view this content.

Thanks, I think I was just being blind, I was indicating the wrong column of data to read.

Can you tell me how to alter this so it will just input the new sheets in the same workbook rather than create a new workbook?

Try like this (untested)

Please Login or Register  to view this content.