Code Export an area to a new file located on the Desktop

[Excelpromax123]

Hi everybody . I want to export an area on the working file to a new file on the dekstop, what should I do?

I would suggest the following procedure:

1. Check if there is a file named "New.XLSX" on the Desktop. If not, create a new file "New.XLSX" . If yes, then Export overwrite it
2. Copy the area to be exported For example: Sheets("data").range("C7:E16").copy to the file "New.XLSX" created on the Desktop and paste the Value at position A1 in Sheet1
3. Close File "New.XLSX"
Sincerely thank everyone

Sincerely thank everyone

 

[Sequoyah]

Hi Excelpromax123,
try this code, if the new file is overwritten anyway it's not necessary to check if it exists, just set DisplayAlerts = False

Sub test()
    
    Dim MyRange As Range
    Dim MyNewFile As String
    Dim Wbk As Workbook
    
    Set MyRange = ThisWorkbook.Sheets("data").Range("C7:E16")
    
    MyNewFile = Environ("USERPROFILE") & "\Desktop\New.xlsx"
          
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    Set Wbk = Workbooks.Add
    MyRange.Copy
    
    With Wbk
        .Sheets(1).Range("A1").PasteSpecial (xlPasteValues)
        .SaveAs MyNewFile
        .Close
    End With
    
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    
End Sub

 

[Excelpromax123]

Xin chào Excelpromax123!
hãy thử mã này, nếu tệp mới bị ghi đè thì không cần thiết phải kiểm tra xem nó có tồn tại hay không, chỉ cần đặt DisplayAlerts = False
[CODE = vba] Kiểm tra phụ ()

Dim MyRange As Range
Dim MyNewFile As String
Dim Wbk As Workbook

Đặt MyRange = ThisWorkbook.Sheets ("data"). Range ("C7: E16")

MyNewFile = Môi trường ("USERPROFILE") & "\ Desktop \ New.xlsx"

Với ứng dụng
.ScreenUpdating = Sai
.DisplayAlerts = Sai
Kết thúc với

Đặt Wbk = Workbooks.Add
MyRange.Copy

Với Wbk
.Sheets (1) .Range ("A1"). PasteSpecial (xlPasteValues)
.SaveAs MyNewFile
.Gần
Kết thúc với

Với ứng dụng
.ScreenUpdating = True
.DisplayAlerts = True
Kết thúc với

Kết thúc Sub [/ CODE]

The code is exactly what I meant. I thank you very much. you're so good

 

[Sequoyah]

HI,
thanks for the feedback, you can mark the thread as solved.