#1
|
|||
|
|||
Exporting workbook specific sheet columns on exit
Hi,
I was wondering if anyone could help. I have a spreadsheet which lists suppliers which gets updated on a regular basis. What I would like to do is have a macro which exports 2 column contents from that spreadsheet upon exit and enters them into another spreadsheet. This is so the linked spreadsheet is always up to date. Not knowing a great deal about Excel, I hope someone can help? Thanks |
#2
|
|||
|
|||
Okay, here is the code that should work. In this sample code, the source workbook has been named "Trial1.xlsm" and the source worksheet as "Sheet1"; the destination workbook as "Trial2.xlsx" and destination worksheet as "Sheet1". The file path to the destination folder has been given as"C:\Trial2.xlsx". You have to change these names and file path according to what you have on your system.
Enter this macro in "This Workbook" code window of the Project Explorer. To do this, open your source workbook, and press Alt+F11. It will open the Macro code window. On the left hand side is the Project Explorer window. Double Click on "This Workbook" and enter the attached code in the blank window that shows up on the right after double clicking. It will be difficult to explain how to link this to the Application.BeforeClose event of Excel, which is what you actually wanted. I suggest that you assign the code I am giving to a button on your Quick Access tool bar (QAT). You can search on the net to find out how to assign a macro to a button on the QAT. You can export the data in the two columns by clicking on the button. |
#3
|
|||
|
|||
Worked an absolute treat - thank you sooo much.
|
#4
|
|||
|
|||
Changed the code to
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wkbk1, wkbk2 As Workbook, wksheet1, wksheet2 As Worksheet, intIndex As Integer 'set the source workbook Set wkbk1 = Application.Workbooks("Trial1.xlsm") 'set the source worksheet .................. |
#5
|
|||
|
|||
Excellent. Happy to help.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
sheet 2 data highlight in sheet 1 | gsrikanth | Excel | 1 | 04-21-2012 06:25 PM |
how to go 75 sheet | gsrikanth | Excel | 5 | 01-17-2012 11:24 PM |
Adding columns in specific rows only | mhays | Excel | 5 | 01-17-2012 09:13 AM |
Construct a summary sheet by summing up from one or more than one sheet. | PRADEEPB270 | Excel | 1 | 11-04-2011 03:46 AM |
copy cell from sheet 2 to sheet 3 macro | slipperyjim | Excel Programming | 1 | 02-18-2010 01:31 AM |