CODEWITHSUNDEEP
excelvbacsvcopy-pastexlsm
Omar
Omar

Reputation: 53

copy and paste from xlsm to csv

I am trying to copy from a macro enabled workbook to a .csv file, but for some reason the paste part paste everything in Column A only. VBA coding is below. Please help me figure out why it will not paste into the same cells as it copies. When I run the macro step by step it works perfect, however when it runs by itself it paste all data in Column A.

Sheets("Input").Select

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Workbooks.Open Filename:="C:\temp\MyFile.csv"
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False

Upvotes: 1

Views: 393

Answers (3)

Omar
Omar

Reputation: 53

@PGSystemTester: This code did the same thing. Everything is pasted in Column A only.

@VBasic2008: This worked, but also gave unexpected errors.

What I did to make it work was basically run the same macro twice. I am not sure why this works. But the first time the macro runs, it pastes everything in Column A. When it runs again, it pastes correctly. So I just doubled the coding for the macro and now it works fine.

Thanks for the help!

Upvotes: 0

VBasic2008
VBasic2008

Reputation: 54807

Export Worksheet to CSV

An Alternative

  • If whatever you are trying to select is the only data in the worksheet, you can just copy the worksheet, which creates a new workbook containing only this worksheet and finally save it as a CSV file.
Option Explicit

Sub ExportWorksheetToCSVtest()
    Const FilePath As String = "C:\temp\MyFile.csv"
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Input")
    ExportWorksheetToCSV ws, FilePath
End Sub

Sub ExportWorksheetToCSV( _
        ByVal ws As Worksheet, _
        ByVal FilePath As String)
    If ws Is Nothing Then Exit Sub
    
    ws.Copy
    With ActiveWorkbook
        Application.DisplayAlerts = False
        .SaveAs FilePath, xlCSV
        Application.DisplayAlerts = True
        .Close SaveChanges:=False
    End With
End Sub

Upvotes: 1

pgSystemTester
pgSystemTester

Reputation: 9907

Updated with improved answer.

Not sure what you're doing wrong with your sheet, but this will work. Don't use Select.

Dim CopyRange As Range, copySheet As Worksheet
Set copySheet = ActiveSheet

Set CopyRange = Intersect(Range("A:C"), copySheet.UsedRange)

Dim theValues()

theValues = CopyRange.Value

Workbooks.Open Filename:="C:\temp\MyFile.csv"
Dim theCSV As Workbook
    Set theCSV = ActiveWorkbook
theCSV.Sheets(1).Range("A1").Resize(UBound(theValues), UBound(theValues, 2)) = theValues

Upvotes: 0

Related Questions