Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trying to copy data from one workbook to another in VBA

Status
Not open for further replies.

primagic

IS-IT--Management
Jul 24, 2008
476
GB
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

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
 
Hi,

You've lost the clipboard.

You need to COPY immediately before you PASTE.
 
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.

 
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.
 
Here's your code modified
Code:
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
 
Out of curiosity, shouldn't this ActiveCell be fully qualified in order to know which workbook the data is pasted to?

Code:
[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.
 
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top