Splitting Sheeets into Separate Files

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,824
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

 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

srizki

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

Forum statistics

Threads
1,171,317
Messages
5,874,960
Members
433,083
Latest member
ychin130

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top