Export siingle rows into new workbook

  • Thread starter Thread starter Hypnotic_Monkey_Scratcher
  • Start date Start date
H

Hypnotic_Monkey_Scratcher

Hi there

I have an excel file full of data (about 1000 rows). I need to export
each row into a new workbook (just one row in each workbook, resulting
in about 1000 files), and then save it so that the filename is the
same as the data from one of the columns. E.g. cell C2 = 'A001-07'
which is what the file should be saved as, and C3 is 'A001-08' which
is what that row's file should be saved as....and so on....

I normally use VBA in access not excel and while I know what I want
todo, I am having a bit of trouble! I guess I will have to put some
kind of a loop in for each row?

Any help and suggestions would be very much appreciated.

Thanks in advance

Andy
 
This may get you started:

Option Explicit
Sub testme03()
Dim wks As Worksheet
Dim NewWks As Worksheet
Dim myRng As Range
Dim myCell As Range

Set wks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With wks
Set myRng = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp))
End With

For Each myCell In myRng.Cells
myCell.EntireRow.Copy _
Destination:=NewWks.Range("A1")
NewWks.Parent.SaveAs _
Filename:="C:\somefolder\" & myCell.Value & ".xls", _
FileFormat:=xlWorkbookNormal
Next myCell

NewWks.Parent.Close savechanges:=False

End Sub
 
Don't use my first suggestion. There's a bug in it.

Option Explicit
Sub testme03()
Dim wks As Worksheet
Dim NewWks As Worksheet
Dim myRng As Range
Dim myCell As Range

Set wks = Worksheets("Sheet1")
Set NewWks = Workbooks.Add(1).Worksheets(1)

With wks
Set myRng = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp))
End With

For Each myCell In myRng.Cells
myCell.EntireRow.Copy _
Destination:=NewWks.Range("A1")
NewWks.Parent.SaveAs _
Filename:="C:\somefolder" & myCell.Value & ".xls", _
FileFormat:=xlWorkbookNormal
Next myCell

NewWks.Parent.Close savechanges:=False

End Sub
 
Back
Top