Saturday, October 15, 2011

How to Copy Workbooks in VBA


1. Open Microsoft Excel. Create a workbook, and name it 'Book1.xls.' Populate the cells with information, and save it to your local disk C: drive.
2. Create another workbook, and name it 'Book2.xls.' Leave it blank, and save it to your local disk C: drive. Close Excel.
3. Open Microsoft Word. Go to 'Tools,' 'Macro' and then Click 'Visual Basic.' This will open the Visual Basic programming environment.
4. Go up to 'Insert' and choose 'Userform.' This will create a Userform.
5. Double-click on the Userform, and paste the following code: FileCopy Source:='C:\Book1.xls', Destination:='C:\Book2.xls'Here is an example of what your code should look like:Private Sub UserForm_Click()FileCopy Source:='C:\Book1.xls', Destination:='C:\Book2.xls'End Sub
6. Press the 'F5' key on your keyboard to run the program. The information in workbook 'Book1.xls' should now be copied to 'Book2.xls' on your local C: drive.

Blogger news