Macros

Resolved · Low Priority · Version 2003

Macros

Linda has attended:
Excel Advanced course
Excel VBA course

by - delegate Linda [7 posts] (2008 Jun 26 Thu, 16:56) Reply

How would I go about recording a macro involving multiple workbooks, can I use recorder?

Version	Date	Location	Places available	Book	Next place rate:
Excel Advanced 1 day course
Version	Date	Location	Places available	Book	Card	Invoice
2007	2008 Nov 27 Thu	Bloomsbury	3	book now	£240	£245
2003	2008 Nov 28 Fri	Southwark	1	book now	£235	£240
2003	2008 Dec 3 Wed	Bloomsbury	0	FULL
2003	2008 Dec 5 Fri	Bayswater	5	book now	£195	£205
2002/XP	2008 Dec 10 Wed	Tooting	0	FULL
2003	2008 Dec 10 Wed	Bloomsbury	0	FULL
Full Schedule: See all 98 Excel Advanced course dates. Bookings currently available until 24th November 2009.

Recording the opening of a workbook

by - trainer Carlos gold contributer [479 posts] (2008 Jul 24 Thu, 13:07) Reply

Hi Linda

The simple answer is YES, you can use the macro recorder to record the code involving multiple workbooks. The example I give below was recorded by me and from an open workbook it opens another workbook, brings some data across and then closes the workbook.

Sub Test

Workbooks.Open Filename:="C:My Documents\Accounts 2007.xls"

Windows("Accounts 2008.xls").Activate
Range("J7").Select
ActiveCell.FormulaR1C1 = "='[Accounts 2007.xls]Sheet1'!R9C4"
Windows("Accounts 2007.xls").Close

End Sub

You can then edit the code for better use; like stopping the screen updating so the user doesn't see the other workbook being accessed.

Hope this helped

Carlos