Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-20-2012, 02:13 AM
shabbaranks shabbaranks is offline Exporting workbook specific sheet columns on exit Windows 7 64bit Exporting workbook specific sheet columns on exit Office 2007
Advanced Beginner
Exporting workbook specific sheet columns on exit
 
Join Date: Mar 2011
Posts: 89
shabbaranks is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 08-21-2012, 05:45 AM
Venky62 Venky62 is offline Exporting workbook specific sheet columns on exit Windows 7 64bit Exporting workbook specific sheet columns on exit Office 2010 32bit
Advanced Beginner
 
Join Date: Jul 2012
Posts: 58
Venky62 is on a distinguished road
Default

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.
Attached Files
File Type: txt SampleCodeTransferDataExcel.txt (1.3 KB, 8 views)
Reply With Quote
  #3  
Old 08-21-2012, 06:50 AM
shabbaranks shabbaranks is offline Exporting workbook specific sheet columns on exit Windows 7 64bit Exporting workbook specific sheet columns on exit Office 2007
Advanced Beginner
Exporting workbook specific sheet columns on exit
 
Join Date: Mar 2011
Posts: 89
shabbaranks is on a distinguished road
Default

Worked an absolute treat - thank you sooo much.
Reply With Quote
  #4  
Old 08-21-2012, 06:54 AM
shabbaranks shabbaranks is offline Exporting workbook specific sheet columns on exit Windows 7 64bit Exporting workbook specific sheet columns on exit Office 2007
Advanced Beginner
Exporting workbook specific sheet columns on exit
 
Join Date: Mar 2011
Posts: 89
shabbaranks is on a distinguished road
Default

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
..................
And now it works when I shut Excel down
Reply With Quote
  #5  
Old 08-21-2012, 08:00 AM
Venky62 Venky62 is offline Exporting workbook specific sheet columns on exit Windows 7 64bit Exporting workbook specific sheet columns on exit Office 2010 32bit
Advanced Beginner
 
Join Date: Jul 2012
Posts: 58
Venky62 is on a distinguished road
Default

Excellent. Happy to help.
Reply With Quote
Reply

Thread Tools
Display Modes


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
Exporting workbook specific sheet columns on exit 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:45 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft