Tuesday, October 18, 2011

How to Link Sheets Using Visual Basic in Excel


1. Press 'Alt' and 'F11' at the same time to open the Visual Basic editor from your Excel worksheet.
2. Click 'Insert > Module' to open a blank code window.
3. Insert the following code (up to 'End Sub') into the blank window:Sub GetValue()
Range('A1').Value = '=[workbook_name.xls]Sheet1!B1'
End SubThis code fetches the value of A1 from the sheet Sheet1 in the workbook workbook_name.xls, then places that value into cell A1 of the currently active sheet.
4. Press 'F5' to display the Macros dialog. The macro name should already be filled in as 'GetValue.'
5. Click 'Run' to execute the VBA code linking the two workbooks together. You should now see the value from A1 in workbook_name.xls in cell A1 of your currently open worksheet.

Blogger news