multiple workfile from one workfile

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there;
I have a file with below structure:
A1: name
A2:E93 contain data
then
A94: name
A95:E186 contain data
then again
continues till:
A5860: name
A5861:E5952 contain data

each set of data are 91 cells (in a coulmn) after each set there is one row
which in its A column is name. Now what I want is how to make an Excel file
for each 91 set of data (91 Col., 5 Rows) and save them with the names comes
in A cell for that data came as name. So basically I shoudl have many files
which contain just one set of data and keep the original file as it is?
Appreciate any help for this.
Best
Darius
 
OK, here's a rough and ready shot at a method for you:-

For c = 1 To 5954 Step 93
newName = Cells(c, 1).Value
If newName = "" Then
Exit Sub
Else
Workbooks.Add
ActiveWorkbook.SaveAs Filename:= _
newName & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
End If
Workbooks("Book1").Activate
Sheets("Sheet1").Select
Range(Cells(c + 1, 1), Cells(c + 6, 5)).Copy
Workbooks(newName).Activate
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Workbooks("Book1").Activate
Sheets("sheet1").Select
Next c

Put this code in your original worksheet and it'll create new files based on
the values in A1, A94 etc (make sure that these will be valid file names) and
preserve your original (change any reference to Book1 to your original
filename and Sheet1 to the correct sheet name)
 
Back
Top