Method 'Paste' of object'_Worksheet' failed :(

[morerockin]

Hey guys,

I want to know if there is anyway you can help me with this. I am trying to run a macro that opens up Workbook A, copies the date from it, and pastes it into a worksheet in a workbook B that was already open. I can get this to work, but it seems that when we add more rows or columns to the report that it is pulling the data from (Worksheet A) it returns this Error when we run the Macro.

It seems that it is failing at:

ActiveSheet.Paste

Is there anything I can do without having to upload the workbook due to the proprietary information?

macro fail.jpg

[Philb1]

I would think you are having the error problem because you're using recorded macro code. If you change ranges or add rows etc, the recorded code can't see any changes you've done

[morerockin]

Hey Philb1,

This will return issues even if I am selecting the entire range during the copy and paste?

[Philb1]

It's impossible to say what is causing the error without seeing an example.
Make up an example workbook with demo data & post it is the best way.
If someone else fixes it overnight, that's good, but if not, I'll have a look in the morning

[morerockin]

Thanks Philb1.

I have created an example, but Excel locks up during the example!

See Attached zip file:Tracker.zip

The File Tracker.xlsx is the file we will be pasting the information into. We will be pasting into the Report tab of that file.

The file, "Report1.xlsx" is the file that we will copy the information from.

As you see in this video, when I manually try to do the paste, Excel locks up:

https://www.youtube.com/watch?v=xhncREeDEKk

[Philb1]

I think I can see what you're trying to do.
Can you post another workbook with that code in it? I don't want to type it myself
Cheers

PS: I watched the video, Where were the dancing girls lol

[morerockin]

Do you need the code for the example?

Lol the video was awesome , I know


Sent from my iPhone using Tapatalk

[Philb1]

The code means I don't have to type 1/2 of it out myself. Pictures are useless

[morerockin]

How do I get the code? Excel crashes at the end so I can't save the macro


Sent from my iPhone using Tapatalk

[Philb1]

I converted the image to a textfile at the site below

http://www.onlineocr.net/

[Philb1]

Ok I've come up with something for you to try. All the details are in a textbox for you to read. Try to do exactly what they say & fingers crossed, it'll work.
Cheers
Phil

Option Explicit

Sub DataImporter()

Dim WkBk2Open As String, FolderPath As String
Dim Wb1 As String, Wb2 As String, Wb3 As String, Wb4 As String
Dim Wsht As Worksheet, Wsht0 As Worksheet
Dim CopyRange As Range
Dim LastCol As Long, LastRow As Long, X As Long

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

    FolderPath = VBA.Environ("UserProfile") & "\Documents\Reports\65MHz Report\"
'    FolderPath = VBA.Environ("UserProfile") & "\Documents\"

    Wb1 = Dir$(FolderPath & "65MHz_Major_Milestone_DRT.xlsxm")
    Wb2 = Dir$(FolderPath & "IM_Milestones_Rev031414_1407333437135.xlsx")
    Wb3 = Dir$(FolderPath & "Supply_Details_-_65MHz.xlsx")
    Wb4 = Dir$(FolderPath & "Consolidated Locked List.xlsx")

'    Wb1 = Dir$(FolderPath & "Book1.xlsb")
'    Wb2 = Dir$(FolderPath & "Book2.xlsb")
'    Wb3 = Dir$(FolderPath & "Book3.xlsb")
'    Wb4 = Dir$(FolderPath & "Book4.xlsb")

On Error GoTo ErrorOut

    For X = 1 To 4
        Select Case X
            Case 1
                WkBk2Open = Wb1
                Set Wsht = ThisWorkbook.Sheets(3)
            Case 2
                WkBk2Open = Wb2
                Set Wsht = ThisWorkbook.Sheets(4)
            Case 3
                WkBk2Open = Wb3
                Set Wsht = ThisWorkbook.Sheets(5)
            Case 4
                WkBk2Open = Wb4
                Set Wsht = ThisWorkbook.Sheets(6)
                Set Wsht0 = ThisWorkbook.Sheets(7)
            Case Else
        End Select
        
        Workbooks.Open (WkBk2Open)
        
        LastCol = Workbooks(WkBk2Open).Sheets(1).Rows(20).Find(what:="*", _
            LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByColumns, _
        searchdirection:=xlPrevious, MatchCase:=False, searchformat:=False).Column
    
        LastRow = Workbooks(WkBk2Open).Sheets(1).Columns(1).Find(what:="*", _
            LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, _
        searchdirection:=xlPrevious, MatchCase:=False, searchformat:=False).Row
    
        Set CopyRange = Workbooks(WkBk2Open).Sheets(1).Range(Workbooks(WkBk2Open) _
            .Sheets(1).Cells(1, 1), Workbooks(WkBk2Open).Sheets(1).Cells(LastRow, LastCol))
        DoEvents
        
        CopyRange.Copy Wsht.Cells(1, 1)
        
        If X = 4 Then
            LastCol = Workbooks(WkBk2Open).Sheets(2).Rows(20).Find(what:="*", _
                LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByColumns, _
            searchdirection:=xlPrevious, MatchCase:=False, searchformat:=False).Column
        
            LastRow = Workbooks(WkBk2Open).Sheets(2).Columns(1).Find(what:="*", _
                LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, _
            searchdirection:=xlPrevious, MatchCase:=False, searchformat:=False).Row
        
            Set CopyRange = Workbooks(WkBk2Open).Sheets(2).Range(Workbooks(WkBk2Open) _
                .Sheets(2).Cells(1, 1), Workbooks(WkBk2Open).Sheets(2).Cells(LastRow, LastCol))
            DoEvents
    
            CopyRange.Copy Wsht0.Cells(1, 1)
        Else: End If
        
        Workbooks(WkBk2Open).Close False
        LastCol = 0
        LastRow = 0
        Set CopyRange = Nothing
    
    Next X

With Application
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
    .EnableEvents = True
    .ScreenUpdating = True
End With
Exit Sub

ErrorOut:
MsgBox " An Error Occured On" & cbcrlf _
            & " The " & X & "th Loop" & cbcrlf _
            & " Macro Will Now Exit"
With Application
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
    .EnableEvents = True
    .ScreenUpdating = True
End With
Exit Sub

End Sub

[morerockin]

Hey PhilB1!

Thank you for your help. However, it returned errors.

2015-05-15 08_12_54-Microsoft Visual Basic for Applications - TrackerINC-MACRO.xlsb [break] - [I.jpg2015-05-15 08_12_26-Microsoft Visual Basic for Applications - TrackerINC-MACRO.xlsb [running] - .jpg

I have confirmed that the worksheets being copied are the furthest left tabs.

There was one workbook left out in this.

I have attached the identical code used:

code.txt

[Philb1]

Sorry that was me. The typo strikes again
Try this attachment

[morerockin]

2015-05-16 09_41_40-Microsoft Excel - TrackerINC-MACRO2.xlsb.jpg

It doesn't look like it got far.

[Philb1]

I see that in your original code the Documents part of the path to where the workbooks are is spelt Docum- ents. Im my code it's Documents. Could be that.
On my computer I have 4 workbooks in the Documents folder named Book1 Book2 Book3 Book4. All 4 workbooks have a copy of the Report sheet data & the book number in a cell to identify which book it's in. Book4 has the same on sheet 1 & sheet 2 & has a cell to identify which book & sheet.
I press the Import Data button & each workbook is opened, the sheets copied & pasted onto the sheets in the Tracker workbook, workbook is closed & the next opened etc. I check each worksheet in the Tracker book & everything is there. I know the code works, so it's something to do with either the Docum- ents spelling or the filenames aren't exactly the same. I'd copy the target workbooks into another folder for safekeeping & experiment a bit. If we can get it to work that far, then maybe it'll be the order things are done that need to be tweaked.