ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

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

Workbooks.open(filepathasstring)

Open Read-only

Workbooks.open(filepathasstring,readonly)


Close A Workbook

Close Without saving changes

Workbooks.close(filepathasstring,false)

Close and Save Changes

Workbooks.close(filepathasstring,true)


Toggle Between Workbooks

Code will be executed on the active workbook.

To activate a workbook

Workbooks(filenameasstring).activate

To return the name of the active workbook

Activeworkbook.name

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

Thisworkbook.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
 
ThisWorkbook.Activate
 



'clears contents in the workbook where the code is stored
 
Range("A2:A10").Cells.ClearContents
 



'toggles to other workbook
 
Workbooks(filename).Activate
 



'Copies data in cells A2 thru A10
 
Range("A2:A10").Copy
 



'paste data into workbook where code resides
 
ThisWorkbook.Activate
 
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.

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    Click to Rate This Article