Runtime error 1004 - Paste method of Worksheet class failed (only when sharing workbook)

[dinkss]

Hi All,

I have a problem with my VBA. I'm getting runtime error 1004 Paste method of Worksheet class failed only when I share my workbook!
Everything is working fine when not shared, and I can't figure out what's wrong! can you please check my VBA and point me what can be wrong?

Any help will be very appreciated.

my VBA:

Sheets("Ward Schedule(5)").Select
Windows("Ward Serac Bausch Schedules USE THIS ONE.xlsx").Activate
Range("D:D,F:F,G:G,I:I,J:J,K:K,L:L,M:M,N:N").Select
Selection.Copy
Windows("KAROL_WAREHOUSE_SCHEDULE.xlsm").Activate
Sheets("Ward (Line 5)").Select
Range("A1:I1").Select
ActiveSheet.Paste
Columns("A:M").EntireColumn.AutoFit
Application.CutCopyMode = False

Selection.AutoFilter

ActiveSheet.Range("A1").Select

Call find_last_cell_2

ActiveWorkbook.Save

Workbooks("Ward Serac Bausch Schedules USE THIS ONE.xlsx").Close False

 

[jasonb75]

Do you have any shapes / objects that are being copied?

If it works when it's not shared then you are likely trying to copy something that is not compatible with a shared workbook. See the 'unsupported features' section on the page linked below.

About the shared workbook feature - Microsoft Support

Shared Workbooks is an older feature that has been replaced by co-authoring. This article explains the older feature, but we recommend using co-authoring instead.

support.microsoft.com

 

[dinkss]

Do you have any shapes / objects that are being copied?

If it works when it's not shared then you are likely trying to copy something that is not compatible with a shared workbook. See the 'unsupported features' section on the page linked below.

About the shared workbook feature - Microsoft Support

Shared Workbooks is an older feature that has been replaced by co-authoring. This article explains the older feature, but we recommend using co-authoring instead.

support.microsoft.com

no shapes or objects, just a bunch of data from one workbook to another workbook. It is only happening with shared file, once I remove share option it is working fine!

Is my code right? Does it have to be in certain steps?

 

[jasonb75]

Is my code right? Does it have to be in certain steps?

It's not very efficient but it should still work. If the code works when it is not shared then it should work when it is shared. Any problems will be caused by something else.
Merged cells in the sheet being copied?
Array formulas?
Data tables?

 

[dinkss]

It's not very efficient but it should still work. If the code works when it is not shared then it should work when it is shared. Any problems will be caused by something else.
Merged cells in the sheet being copied?
Array formulas?
Data tables?

I got error saying array formulas could not be copied.

How can I improve my code to be efficient and effective? To work better?

Is there a way for fixing this when workbook is shared?

 

[jasonb75]

Is there a way for fixing this when workbook is shared?

See the link that I provided earlier, there is a section at the top that gives an alternative method (called co authoring if I remember correctly, it is something I have not had cause to use but I believe it is what you will need).

As far as efficiency goes, one of the first things to learn is how to do things without selecting / activating. I would personally use something like this (quick untested example).

Sub test()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Set wb1 = Workbooks("Ward Serac Bausch Schedules USE THIS ONE.xlsx")
Set wb2 = Workbooks("KAROL_WAREHOUSE_SCHEDULE.xlsm")
Set ws1 = wb1.Worksheets("Name_Of_Sheet_to_Copy")
Set ws2 = wb2.Worksheets("Ward (Line 5)")

Intersect(ws1.Range("D:D,F:F,G:G,I:I,J:J,K:K,L:L,M:M,N:N"), ws1.UsedRange).Copy ws2.Range("A1")
With ws2.Range("A1:M1")
    .EntireColumn.AutoFit
    .AutoFilter
End With
    Call find_last_cell_2
ActiveWorkbook.Save
wb1.Close False
End Sub

 

[dinkss]

See the link that I provided earlier, there is a section at the top that gives an alternative method (called co authoring if I remember correctly, it is something I have not had cause to use but I believe it is what you will need).

As far as efficiency goes, one of the first things to learn is how to do things without selecting / activating. I would personally use something like this (quick untested example).

Sub test()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Set wb1 = Workbooks("Ward Serac Bausch Schedules USE THIS ONE.xlsx")
Set wb2 = Workbooks("KAROL_WAREHOUSE_SCHEDULE.xlsm")
Set ws1 = wb1.Worksheets("Name_Of_Sheet_to_Copy")
Set ws2 = wb2.Worksheets("Ward (Line 5)")

Intersect(ws1.Range("D:D,F:F,G:G,I:I,J:J,K:K,L:L,M:M,N:N"), ws1.UsedRange).Copy ws2.Range("A1")
With ws2.Range("A1:M1")
    .EntireColumn.AutoFit
    .AutoFilter
End With
    Call find_last_cell_2
ActiveWorkbook.Save
wb1.Close False
End Sub

I will try this, perfect ? thank you. Will let you know if it works.

 

[dinkss]

I will try this, perfect ? thank you. Will let you know if it works.

I did try the code - it doesn't work as it is not opening file from remote location like my code did,

I'm getting error in this section:
Set wb1 = Workbooks("Ward Serac Bausch Schedules USE THIS ONE.xlsx")

 

[jasonb75]

The code that you posted doesn't open a workbook, it activates one that is already open.

 

[dinkss]

The code that you posted doesn't open a workbook, it activates one that is already open.

sorry, I missed this bit:

Workbooks.Open Filename:= _
""