CODEWITHSUNDEEP
excelvba
Avanti Radke
Avanti Radke

Reputation: 1

Error 1004 Paste Method of Worksheet class failed

I am trying to copy data from multiple workbooks into a single one. Following is the code I am using. but is is giving error 1004 "Paste Method of Workshet class failed and stuck at code ActiveSheet.Paste when debug. Any help is highly appreciated.


Public Sub ImportTextFile()
    Dim TextFile As Workbook
    Dim OpenFiles() As Variant
    Dim i As Integer
    
    
    OpenFiles = Application.GetOpenFilename(Title:="Select Files(s) to Import", MultiSelect:=True)
    
    Application.ScreenUpdating = False
    
        For i = 1 To Application.CountA(OpenFiles)
        
    Set TextFile = Workbooks.Open(OpenFiles(i))
    
    TextFile.Sheets(1).Range("A1").CurrentRegion.Copy
    Workbooks(1).Activate
    Workbooks(1).Worksheets.Add
    ActiveSheet.Paste
    ActiveSheet.Name = TextFile.Name
    
    Application.CutCopyMode = False
    
    TextFile.Close
    Next i

    
    Application.ScreenUpdating = True
    
End Sub

Upvotes: 0

Views: 96

Answers (1)

Mikku
Mikku

Reputation: 6654

Try this:

Dim ws as Worksheet

Declaring the ws object.

TextFile.Sheets(1).Range("A1").CurrentRegion.Copy
Set ws = Workbooks(1).Worksheets.Add
ws.Range("A1").PasteSpecial xlPasteAll
ws.Name = TextFile.Name

instead of activation, you can set the objects. This will be faster as well.

Upvotes: 0

Related Questions