Trying to copy data from one workbook to another in VBA

[primagic]

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

ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Full 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

 

[SkipVought]

Hi,

You've lost the clipboard.

You need to COPY immediately before you PASTE.

 

[primagic]

How do I do this between seperate workbooks?

At the moment the code does the copy, opens the new workbook, sets where to paste but doesn't paste.

The paste range in the new workbook is alway dynamic by selecting the next available cell

So I cant set a fixed range of the new workbook to paste.

 

[SkipVought]

Just before you do the paste, you must go back to the original workbook/sheet/range and COPY, switch to the workbook/sheet/cell you want to paste in, then PASTE.

 

[SkipVought]

Here's your code modified

Sub CreateUKPBFile()
    Dim wb As Workbook
    Dim xlFile As String

    Application.DisplayAlerts = False
    
    
    With Application.ScreenUpdating = False
            
            
    With ThisWorkbook
        .Sheets("Control Sheet").Range("D3").Value = "UKPB"
    
        
        'Download spreadsheet from Sharepoint so it can be edited
        xlFile = "[URL unfurl="true"]http://xxx"[/URL]
        
        'Determine if workbook can be checked out
        If Workbooks.CanCheckOut(xlFile) = True Then
            Workbooks.CheckOut xlFile
            
            
            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).Offset(0, 1).Select
        
        'copy the range from the source workbook
            .Sheets("New Balances").Range("S5:S50").Copy
        
            '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 With
    
    Set wb = Nothing
End Sub

 

[Andrzejek]

Out of curiosity, shouldn't this ActiveCell be fully qualified in order to know which workbook the data is pasted to?

[green]
'copy the range from the source workbook[/green]
.Sheets("New Balances").Range("S5:S50").Copy
        [green]
'Paste values on previous column ******************[/green]
[red]wb.[/red]ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

By implication, it is the active workbook/sheet/cell.

For instance there is a cell selected on some sheet in your other workbook, the one you copy from, but it's not ACTIVE.

If you were coding in another application, then you would need such a reference.

 

[primagic]

Got it working.

Thank you.

 

[Andrzejek]

It would be nice to mark the helpful post with the Star so others who look for the solution would know what to do.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.