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.

Sub InventoryData_Button1_Click()
 Dim UsedRows As Long
 Dim UsedColumns As Long
  Dim i As Long, j As Long
 
 '// Define a suitable file name
 Open "E:\1. Inventory\Inventory.txt" For Output As #1
 With ActiveSheet
 UsedRows = .UsedRange.Rows.Count
 UsedColumns = .UsedRange.Columns.Count
 For i = 4 To UsedRows + 2
 For j = 1 To UsedColumns - 1
 Print #1, .Cells(i, j); "|";
 Next j
 Print #1, .Cells(i, UsedColumns)
 Next i
 End With
 
 Close #1
 MsgBox "Finished...", vbInformation
 
End Sub

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

Sub InventoryData_Button3_Click()
  Set NewBook = Workbooks.Add
  Workbooks("Support.xlsm").Worksheets("Data").Range("A3:O1000").Copy
  NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)
  NewBook.SaveAs FileName:=NewBook.Worksheets("Sheet1").Range("E3").Value
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.

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.

Thank you again. I think it will be easy for me to describe with a file example.