I have the following code so far. From one workbook, I have it opening another workbook from sharepoint. this works fine.
I am trying to copy data from the original workbook into the opened sharepoint file, however I am getting the error 'PasteSpecial method of Range class failed' on the line
Full code
I am trying to copy data from the original workbook into the opened sharepoint file, however I am getting the error 'PasteSpecial method of Range class failed' on the line
Code:
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Full code
Code:
Sub CreateUKPBFile()
Application.DisplayAlerts = False
With Application
.ScreenUpdating = False
'Make the Control Sheet Active
ActiveWorkbook.Sheets("Control Sheet").Activate
'Change segment selection to UKPB
Range("D3").Value = "UKPB"
'Make the New Balances Sheet Active
ActiveWorkbook.Sheets("New Balances").Activate
'Select the refreshed data to be copied
ActiveSheet.Range("S5:S50").Copy
'Download spreadsheet from Sharepoint so it can be edited
Dim xlApp As Excel.Application
Dim wb As Workbook
Dim xlFile As String
xlFile = "[URL unfurl="true"]http://xxx"[/URL]
'Determine if workbook can be checked out
If Workbooks.CanCheckOut(xlFile) = True Then
Workbooks.CheckOut xlFile
'Create instance of Excel
'Set xlApp = New Excel.Application
'xlApp.Visible = True
'xlApp.DisplayAlerts = False
Set wb = Workbooks.Open(xlFile, , False)
'Select the correct sheet
wb.Sheets("New Balances").Activate
'Find the last used cell on the row
'wb.ActiveSheet.Range("AE5").End(xlToLeft).Select
wb.Sheets("New Balances").Range("AE5").End(xlToLeft).Select
'Move to the next empty cell
ActiveCell.Offset(0, 1).Select
'Paste values on previous column ******************
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Else
MsgBox "Your are unable to check out this document at this time."
End If
End Sub