CODEWITHSUNDEEP
excelvbaexporttxt
Juan Lantigua
Juan Lantigua

Reputation: 11

Copy and Paste Area are not the Same

I want to copy a specific range on one of my excel sheets to a txt file but when I use the code:

Sub FromExcelToNpad()
    'export activesheet as txt file
    Dim myPath As String, myFile As String
    myPath = ThisWorkbook.Path & "\"
    myFile = "z.txt"
    Dim WB As Workbook, newWB As Workbook
    Set WB = ThisWorkbook
    Application.ScreenUpdating = False
    Set newWB = Workbooks.Add
    WB.ActiveSheet.UsedRange.Copy newWB.Sheets(1).Range("A4,A10:A22,A28")
    With newWB
        Application.DisplayAlerts = False
        .SaveAs Filename:=myPath & myFile, FileFormat:=xlText
        .Close True
        Application.DisplayAlerts = True
    End With
    WB.Save
    Application.ScreenUpdating = True
    End Sub

keep getting the copy and paste error, it works if I used just range("A4") but it ends up copying the entire sheet to the txt.

Upvotes: 0

Views: 126

Answers (1)

Juan Lantigua
Juan Lantigua

Reputation: 11

Sub FromExcelToNpad()
    'export activesheet as txt file
    Dim myPath As String, myFile As String
    myPath = ThisWorkbook.Path & "\"
    myFile = "z.txt"
    Dim WB As Workbook, newWB As Workbook
    Set WB = ThisWorkbook
    Application.ScreenUpdating = False
    Set newWB = Workbooks.Add
    WB.ActiveSheet.Range("A4,A10:A22,A28").Copy newWB.Sheets(1).Range("A4")
    With newWB
        Application.DisplayAlerts = False
        .SaveAs Filename:=myPath & myFile, FileFormat:=xlText
        .Close True
        Application.DisplayAlerts = True
    End With
    WB.Save
    Application.ScreenUpdating = True
End Sub

Thanks @bigben, your comment was enough for me to fix my issue!!!

Upvotes: 1

Related Questions