Splitting Sheeets into Separate Files

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 
Last edited:

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 
Last edited:

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
It says Compile Error
Invalid outside procedure

 

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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:


 

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,791
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You need to find the function LastRowInOneColumn wherever you found that VBA code and paste it into your module.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,788
Messages
5,524,879
Members
409,609
Latest member
Channingz

This Week's Hot Topics

Top