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]

You will need to edit the name in the Workbooks.Open line to include the path as with your existing code. This checks to see if it is already open first.

Sub test()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
On Error Resume Next
    Set wb1 = Workbooks("Ward Serac Bausch Schedules USE THIS ONE.xlsx")
On Error GoTo 0
If wb1 Is Nothing Then Set wb1 = Workbooks.Open("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]

You will need to edit the name in the Workbooks.Open line to include the path as with your existing code. This checks to see if it is already open first.

Sub test()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
On Error Resume Next
    Set wb1 = Workbooks("Ward Serac Bausch Schedules USE THIS ONE.xlsx")
On Error GoTo 0
If wb1 Is Nothing Then Set wb1 = Workbooks.Open("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

 

[dinkss]

I will try this

Your VBA works perfectly when I changed location, but as soon as I will share it (old sharing option) it breaks it and run into run time error 1004

 

[jasonb75]

but as soon as I will share it (old sharing option) it breaks it and run into run time error 1004

The code was only written to be more efficient. Shared workbooks are incompatible with the same things regardless of the code used for the procedure.

Unless you rewrite your workbook without the things that are causing the problem your only options to get around the error that you are encountering are to use the new co authoring option (if it works with that) or to unshare the workbook before running the code.

 

[dinkss]

The code was only written to be more efficient. Shared workbooks are incompatible with the same things regardless of the code used for the procedure.

Unless you rewrite your workbook without the things that are causing the problem your only options to get around the error that you are encountering are to use the new co authoring option (if it works with that) or to unshare the workbook before running the code.

Ok perfect ? thank you

 

[dinkss]

Ok perfect ? thank you

Could you help me out with this? Or would you know how to do it?

Data to stay assigned to cells even when updated

I have designed schedule for all operators but I have a problem when data is updated. When planners are updating schedule for production and operators have all information in the schedule selected - like staged, released etc that information keeps moving up or down when data is pulled from live...

www.mrexcel.com