CODEWITHSUNDEEP
vbaexcelcsv
IneedCoffee
IneedCoffee

Reputation: 1

VBA MACRO unable to paste

Please help me... this is driving me nuts.

I am trying to copy some data from a CSV but unable to paste it to the destination file that I normally do manually.

The issue I am facing: -I cannot go back to the destination file's sheet -Even if I could it would paste as string -I need the data to be identical from the CSV

 MyFile = Application.GetOpenFilename()

    ChDir "C:\datafolder\"

    Application.Workbooks.Open (MyFile)
    Range("A1").CurrentRegion.Select
    Selection.copy
    ActiveWorkbook.Close savechanges:=False
    Application.ScreenUpdating = True

    Windows("chickenfeed.xlsm").Activate
    ActiveWorkbook.Sheets("Raw Export").Range("A1").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End sub

Upvotes: 0

Views: 488

Answers (1)

PatricK
PatricK

Reputation: 6433

You closed the source range before the paste.

Try this and learn the flow...
(Assuming it's "Sheet1" you try to copy data from)

Option Explicit

Sub PasteData()
    Dim oSourceWB As Workbook, oTargetWB As Workbook, MyFile As String

    MyFile = Application.GetOpenFilename()

    ChDir "C:\datafolder\"
    On Error Resume Next
    Set oSourceWB = Workbooks.Open(Filename:=MyFile, ReadOnly:=True)
    Set oTargetWB = Workbooks("chickenfeed.xlsm")
    On Error GoTo 0
    If Not (oSourceWB Is Nothing And oTargetWB Is Nothing) Then
        oSourceWB.Worksheets("Sheet1").Range("A1").CurrentRegion.Copy oTargetWB.Sheets("Raw Export").Range("A1")
        oSourceWB.Close SaveChanges:=False
    End If
    Set oSourceWB = Nothing
    Set oTargetWB = Nothing
End Sub

Upvotes: 1

Related Questions