Copying and pasting from a different workbook | Excel forum

Resolved · High Priority · Version 2010

Mon 2 Feb 2015, 18:10 Delegate Freddie said...

Freddie has attended:
Excel VBA Introduction course

Copying and pasting from a different workbook

Hi Guys

Firstly, many thanks for your response on my other question -it worked a treat!

I've come up against another obstacle though in my macro.

I'm trying to copy and paste from another workbook, but my code is incorrect - are you able to help?

The file path where I want to copy from is:

C:\Users\Desktop\Macro Project\52 CCY + Market for Static Data Checks.xlsx"
Cells.Select
Range("A1:B212").Copy

And the file I want to paste to is a worksheet called "BBA" in the below path: (in cell A312)
C:\Users\Desktop\Macro Project\MacroTes3.xls")

Are you able to assist?
Thanks!
Freddie

Tue 3 Feb 2015, 10:51 Trainer Jens said...

RE: Copying and pasting from a different workbook

Hi Freddie,

Thank you for the forum question.

Please copy and paste the code below to a module in the destination workbook.

Sub CopyFromAnotherWorkbook()

''''''The code below is if the source workbook is not open.

Dim WkBook As Workbook

''''''will open the source workbook. You will need your own path below.
Set WkBook = Workbooks.Open("C:\Users\Admin\Desktop\EMPLOY4.xlsx")

''''' You will need to change the sheet name and range in thee line below (the sheet and range in the source workbook).
ActiveWorkbook.Sheets("Employees").Range("a5:g33").Copy

ThisWorkbook.Activate

Sheets(1).Range("b2").Select

'''''''' paste is an activesheet member
ActiveSheet.Paste

'''''will empty the clipboard
Application.CutCopyMode = False

WkBook.Close savechanges:=False

End Sub

I hope this is what you are looking for but please let me know if you have any problems.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Turn Function tooltips on and off

Excel 2002 (XP) and Excel 2003 have the Function tooltips facility. When you type in a function name followed by a bracket, for example, =IF(, a yellow box appears beside the function name and lists the function's arguments. This is very useful when you can't quite remember the order of a function's arguments or what the arguments actually are!

However, Function tooltips can become annoying. To turn them off, choose Tools|Options. and select the General tab. Then, untick the Function tooltips box and choose OK.

View all Excel hints and tips