ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Science & Programming»
  • Programming Languages

VBA: Activate, Open, and, Close Excel Workbooks

Updated on September 11, 2012

Moving between sheets in a workbook

One of the first challenges you will find when beginning to write VBA is moving between sheets.

You will soon find this is no challenge at all.

You can select as sheets as follows:

Sheets("Sheet Name").select

Once you begin to implement VBA for real tasks. It won't be long before you find the need to work with other workbooks. To do this I you will use the workbooks method.

Open a Workbook

Open Read-only,readonly)

Close A Workbook

Close Without saving changes


Close and Save Changes


Toggle Between Workbooks

Code will be executed on the active workbook.

To activate a workbook


To return the name of the active workbook

It is handy to store the filename as string after opening the file in case it ever changes.

This Workbook Method

When working with the workbook in which the code reside you can use the thisworkbook method.

To Close Without saving changes

Thisworkbook.close false

To Close and Save changes

Thisworkbook.close true

To activate


A Sample Macro

Sub testmynewskills()

'defines string variables
Dim filepath As String
Dim filename As String

'store file past locate in cell A1 in the variable file path
filepath = Range("A1").Value

'opens the file path in cell A1
Workbooks.Open (filepath)

'store the filename of the newly open workbook to the variable filename
filename = ActiveWorkbook.Name

'activates the workbook where the code it stored

'clears contents in the workbook where the code is stored

'toggles to other workbook

'Copies data in cells A2 thru A10

'paste data into workbook where code resides
Range("A2:A10").PasteSpecial (xlPasteAll)

'close workbook without saving changes
Workbooks(filename).Close False
End Sub

Two Important Notes about Copying and Pasting Between Workbooks

  • Always paste data before closing the workbook in which it resides. When a workbook is close the clipboard is cleared. This one gets me every time.
  • Always paste values when pasting from a protected sheet. If you paste all from a protected sheet the cells on your new sheet will become protected.


    0 of 8192 characters used
    Post Comment

    No comments yet.