PDA

View Full Version : [SOLVED] VBA: copy/pastespecial between workbooks



bc_actuary
07-22-2016, 09:25 AM
Hi all,

I am attempting to write a macro to copy and paste values between two open workbooks. The range that the data needs to be pasted to has to be selected by the user, and I've used Application.InputBox with Type=8 to achieve this. When I run the code, Excel throws error '1004'. Debugging revealed that the problem is in the PasteSpecial line, and when I mouse over it says "False=False". I have tried recording a similar macro to try and figure this out to no avail. I'm fairly new to VBA and I thought this would be easy, but apparently not :think:

Here's what I'm working with:


Sub pastespecial_between_workbooks()


Windows("workbook_to_copy_from.xlsx").Activate

Range("J15:J26").Select
Selection.Copy

Windows("workbook_to_paste_to.xlsx").Activate

Dim paste_cell As Range
Set paste_cell = Application.InputBox("Select where to paste", Type:=8)

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

End Sub


Any ideas are welcome, thanks!

p45cal
07-22-2016, 10:11 AM
It seems to lose the clipboard contents on execution of the Set paste_cell line, so find the destination first, then go back and copy the data and paste it in the destination:
Sub pastespecial_between_workbooks()
Dim paste_cell As Range
Windows("workbook_to_paste_to.xlsx").Activate
Set paste_cell = Application.InputBox("Select where to paste", Type:=8)

Windows("workbook_to_copy_from.xlsx").Activate
Range("J15:J26").Copy
paste_cell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.Goto paste_cell
End Sub

If you know the sheet name of the source data then you can use this:
Sub blah()
Dim paste_cell As Range
Windows("workbook_to_paste_to.xlsx").Activate
Set paste_cell = Application.InputBox("Select where to paste", Type:=8)
paste_cell.Resize(12).Value = Workbooks("workbook_to_copy_from.xlsx").Sheets("TheSourceSheetNameHere").Range("J15:J26").Value
End Sub
Obviously, change TheSourceSheetNameHere in the code to the actual sheet name.

bc_actuary
07-22-2016, 10:19 AM
That seems to have done the trick, thank you!