vba to copy used range ,format text and save in directory

[RAM72]

Hi
Copy used range as per attached as sample , format used range as text format to last data row, there are blank columns in the middle of used range

add a new workbook with 1 sheet only rename sheet tab as cell b1 of summary workbook (note there is a /) so to rename e.g aaa_10 and save file in csv ms dos format in c:\\upload which already exists.

 

[Marc L]

Hi !​

(note there is a /) so to rename e.g aaa_10 and save file in csv​

So once you have corrected B1 cell, run this demonstration
to save Sheet2 used range data to a csv text file :​

Sub Demo1()
     Const FOLDER = "C:\upload\"
        On Error Resume Next
    If Dir(FOLDER & ".") = "" Then Beep: Exit Sub
        On Error GoTo 0
    With Sheet2.UsedRange.Rows
            If .Cells(2).Value = "" Then Beep: Exit Sub
            FF% = FreeFile
            Open FOLDER & .Cells(2).Value & ".csv" For Output As #FF
        For R& = 1 To .Count
            Print #FF, Join(Application.Index(.Item(R).Resize(2).Value, 1), ",")
        Next
            Close #FF
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[RAM72]

Hi !​

So once you have corrected B1 cell, run this demonstration
to save Sheet2 used range data to a csv text file :​

Sub Demo1()
     Const FOLDER = "C:\upload\"
        On Error Resume Next
    If Dir(FOLDER & ".") = "" Then Beep: Exit Sub
        On Error GoTo 0
    With Sheet2.UsedRange.Rows
            If .Cells(2).Value = "" Then Beep: Exit Sub
            FF% = FreeFile
            Open FOLDER & .Cells(2).Value & ".csv" For Output As #FF
        For R& = 1 To .Count
            Print #FF, Join(Application.Index(.Item(R).Resize(2).Value, 1), ",")
        Next
            Close #FF
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

Hi
Tested but getting some issues, try on desktop and c:\\upload
Got same error run time error 424 object required

With Sheet2.UsedRange.Rows in yellow.

Advise where I am going wrong

 

[Marc L]

Did you at least test with your attachment ? As it rocks on my side …

You can change the Sheet2 CodeName
to the Worsheets reference by an index or a name …
​

______________________________________________________________

Mod edit : thread closed (wild cross posting aka JEAN1972 !)

​