Save a range of data of a worksheet to a new workbook to specific location.

  • Hi There,

    I am stuck, couldn't get the solution. Hope some one help me out.

    I have a worksheet, the data range is A3:O1000.

    Now I have to save data in two format (. text & .xlsx) in a specific location with same file name (File Name based on cell Value= The value in A1 as Name, with C1 as Entry Number and E1 as date)

    I have to use one command button to do the job.

    The text file must create as "pipe delimited" format (no space) from range A4 to last data row.

    For text file I am using the below code but It create "pipe delimited" format with space.

    and the .xlsx file create from A3 to last data row. I am Using below code.

    Code
    1. Sub InventoryData_Button3_Click()
    2. Set NewBook = Workbooks.Add
    3. Workbooks("Support.xlsm").Worksheets("Data").Range("A3:O1000").Copy
    4. NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)
    5. NewBook.SaveAs FileName:=NewBook.Worksheets("Sheet1").Range("E3").Value
    6. End Sub


    The file creates but I had to save manually to my desired location.

    What should I have to do to change in the code to do the job as expected.


    Thanks in advance.

  • Try this for the workbook, change the Path in the code


    Code
    1. Sub InventoryData_Button3_Click()
    2. Const sPath As String = "C:\Temp\" '<- change the path
    3. Workbooks("Support.xlsm").Worksheets("Data").Copy
    4. With ActiveWorkbook
    5. .Worksheets("Sheet1").Cells.Value = .Worksheets("Sheet1").Cells.Value
    6. ThisWorkbook.SaveAs sPath & .Worksheets("Sheet1").Range("E3").Value, 51
    7. End With
    8. End Sub
  • Thanks for your response,

    The above code gives run time error "9"

    Subscript out of range.


    Is there any way to combine the above two codes to run with one command button? Your code create a copy of my worksheet but not a range of data. Sorry, I am not good at describing my thoughts:?:.


    By the way thanks again.