Macro to export range to seperate sheet XLS format

[bushidowarrior]

Okay, I am not quite sure to explain this, but here goes:

1. When I run this marco
2. It takes the range 2 - 6 and across filled with data.
3. It exports the files as Title 01.xls.
4. The name is obtained from the A1
5. Then same happens for Title 02 and all the USED rage beneath it.
6. The VBA look for one empty row. That is the indicator that it is one sheet.
7. At the send I will have 2 files Title 01.xls and Title 02.xls.

I hope I have explained it well.

Thank you all very much!!!

 

[rpaulson]

Bush,

let me know how this works.

Sub do_it()
sr = 1

For r = 1 To Cells(Rows.Count, "A").End(xlUp).Row + 1

If Cells(r, "A") = "" Then

lr = r - 1

myPath = "C:\Results\"
myFile = myPath & Cells(sr, "A") & ".xls"

Rows(sr & ":" & lr).Copy

  Set NewBook = Workbooks.Add
  
  NewBook.Worksheets("Sheet1").Range("A1").Select ' Special (xlPasteValues)(xlPasteformat)
    ActiveSheet.Paste

NewBook.SaveAs Filename:=myFile
ActiveWorkbook.Close

sr = r + 1
End If

Next r

End Sub

enjoy,
Ross

 

[bushidowarrior]

Hello Almost there.

I think we have to use this:

    ActiveWorkbook.SaveAs Filename:=relativePath, FileFormat:=56 'xlExcel8 (97-2003 format xls)

and I would like the files to be create in the same folder as the excel file that is use to execute the VBA.

I am playing around with the code now.

Bush,

let me know how this works.

Sub do_it()
sr = 1

For r = 1 To Cells(Rows.Count, "A").End(xlUp).Row + 1

If Cells(r, "A") = "" Then

lr = r - 1

myPath = "C:\Results\"
myFile = myPath & Cells(sr, "A") & ".xls"

Rows(sr & ":" & lr).Copy

  Set NewBook = Workbooks.Add
  
  NewBook.Worksheets("Sheet1").Range("A1").Select ' Special (xlPasteValues)(xlPasteformat)
    ActiveSheet.Paste

NewBook.SaveAs Filename:=myFile
ActiveWorkbook.Close

sr = r + 1
End If

Next r

End Sub

enjoy,
Ross

 

[rpaulson]

myPath =  Application.ActiveWorkbook.Path & "\"