Splitting Sheeets into Separate Files

[srizki]

Can anyone please tell me the code for splitting sheets in to different files.
I have searched on Google and find two codes and got compiling error.

Thanks

 

[Andrew Poulsom]

What code did you find that's causing an error?

 

[srizki]

For Each ws In Worksheets(Array("Mary", "Susan"))
SheetName = ws.Name
ws.Copy

With ActiveWorkbook
.SaveAs Filename:=MyFilePath _
& "\" & SheetName & ".xls"
.Close SaveChanges:=True
End With

Next ws



I will change Mary and Susan

 

[Andrew Poulsom]

What error are you getting and what have you assigned to the variable MyFilePath?

 

[srizki]

I did not change anything in the code, I left it as is so I can do that later, after the sheets have been separated.

, but my file path is,

C:\Sohail\LITIGATION PREPARATION-Hanh\ALLOCATION DATA\Hanh Project\6-30-2014\Allocation 2014-2015\Split Allocation\Alloc Rev 2015 Combine_RC Sheets Separate sheets

Thanks
After splitting the sheets, I need to send them to different managers of that departments, and I do not want to send them macro sheets. Would sheets have macro attached to them?

Oh Andrew, you asked wheat error I am getting, please wait, I will run again.

Thanks Andrew.

 

[srizki]

It says Compile Error
Invalid outside procedure

 

[srizki]

Another code I used was:

Sub CreateNewWBS()
Dim wbThis As Workbook
Dim wbNew As Workbook
Dim ws As Worksheet
Dim strFilename As String

Set wbThis = ThisWorkbook
For Each ws In wbThis.Worksheets
strFilename = wbThis.Path & "/" & ws.Name
ws.Copy
Set wbNew = ActiveWorkbook
wbNew.SaveAs strFilename
wbNew.Close
Next ws
End Sub

I worked for some times and then it gave an error,
Run-rime error ‘1004’
The file could not be accessed. Try one of the following:
…

 

[Andrew Poulsom]

Please post ALL your VBA code.

 

[srizki]

I copied and paste codes from Google search, the following is one of them,

Sub copyPasteData()
Dim strSourceSheet As String
Dim strDestinationSheet As String
Dim lastRow As Long
strSourceSheet = "Data entry"
Sheets(strSourceSheet).Visible = True
Sheets(strSourceSheet).Select
Range("C2").Select
Do While ActiveCell.Value <> ""
strDestinationSheet = ActiveCell.Value
ActiveCell.Offset(0, -2).Resize(1, ActiveCell.CurrentRegion.Columns.Count).Select
Selection.Copy
Sheets(strDestinationSheet).Visible = True
Sheets(strDestinationSheet).Select
lastRow = LastRowInOneColumn("A")
Cells(lastRow + 1, 1).Select
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets(strSourceSheet).Select
ActiveCell.Offset(0, 2).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub

That says,



Compile Error
Sub or Function not defined

 

[Andrew Poulsom]

You need to find the function LastRowInOneColumn wherever you found that VBA code and paste it into your module.