VBA: Activate, Open, and, Close Excel Workbooks
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:
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
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
To Close and Save changes
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.