Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-31-2011, 12:30 AM
virsojour virsojour is offline macro to transfer data from one workbook to another workbook Windows XP macro to transfer data from one workbook to another workbook Office 2007
Novice
macro to transfer data from one workbook to another workbook
 
Join Date: Jun 2010
Posts: 5
virsojour is on a distinguished road
Default macro to transfer data from one workbook to another workbook

I made research around the site for a similar post, but i can't seem to construct my own from them (I've almost no knowledge with excel macros). Please help



Say you have a workbook (named inventory1.xls) of 1 sheet of 5 columns (A - E). How can you transfer automatically all data from that workbooks single sheet to another workbook (named consolidated.xls), also in a single sheet. With the workbook (consolidated.xls) automatically updated when data is added in the source workbook (inventory1.xls).

Thank you very much.
This will be used to help a non-profit NGO to improve their inventory tracking.
Thank you very much again.
Reply With Quote
  #2  
Old 01-31-2011, 01:59 AM
macropod's Avatar
macropod macropod is offline macro to transfer data from one workbook to another workbook Windows 7 32bit macro to transfer data from one workbook to another workbook Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi virsojour,

Is there a reason both sets of data can't be kept in the same workbook (eg on different worksheets)? And what is the purpose of the data replication?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 01-31-2011, 05:55 AM
virsojour virsojour is offline macro to transfer data from one workbook to another workbook Windows XP macro to transfer data from one workbook to another workbook Office 2007
Novice
macro to transfer data from one workbook to another workbook
 
Join Date: Jun 2010
Posts: 5
virsojour is on a distinguished road
Default

Macropod,

I plan to link excel files between pcs in a network. If not possible through pcs in a network. At least it would be easier for users to select the only file they need to update, and not have to open the consolidated file and browse through sheets...

Thanks macropod
Reply With Quote
  #4  
Old 01-31-2011, 04:55 PM
macropod's Avatar
macropod macropod is offline macro to transfer data from one workbook to another workbook Windows 7 32bit macro to transfer data from one workbook to another workbook Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi virsojour,

Do the other PCs add/delete items on the inventory (eg as in adding/deleting managed furniture items), or simply adjust quantities (as in adding/deleting stationery from a store)? If they're adjusting quantities, do they work with their own stocks, or do they all add/delete quantities from a common pool?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 02-01-2011, 07:18 PM
virsojour virsojour is offline macro to transfer data from one workbook to another workbook Windows XP macro to transfer data from one workbook to another workbook Office 2007
Novice
macro to transfer data from one workbook to another workbook
 
Join Date: Jun 2010
Posts: 5
virsojour is on a distinguished road
Default

Macropod,

They will be adding items on the inventory, and sometimes delete also.
There is also the possibility of adjusting the quantities of items. Adjusting of quantities will simply be done on a common pool.

Thank you very much again macropod.
Reply With Quote
  #6  
Old 02-01-2011, 08:58 PM
macropod's Avatar
macropod macropod is offline macro to transfer data from one workbook to another workbook Windows 7 32bit macro to transfer data from one workbook to another workbook Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi virsojour,

What kind of inventories are you working with (eg individual furniture/machinery items or bulk items like stationery)? For either kind of inventory, it is undesirable to allow every user to simply add/delete inventory items on their own workbooks without some form of control over what can be added/deleted. if you don't have such controls, that can cause problems when differrent users describe the same items in different ways. For example, generating a consolidated inventory becomes very difficult.

For an inventory of individual items, you need to have a master list with a lookup table from which users can select item types to add/delete records as items are acquired disposed of. For an inventory of bulk items, it's best to have all items listed in a 'master' workbook only and to only allow the users to adjust their quantities.

The above considerations dictate the way data from the different workbooks should be linked. In both cases, the master lists of inventory items should reside in a 'master' workbook, to which the other workbooks are linked. There is no need to use macros for this part - it's simply a matter of using formulae to link the data in both sets of workbooks. For an inventory of individual items, the master workbook would require a macro that can interrogate the users' workbooks and tally the data for each defined item - the users' workbooks wouldn't require any macros. This updating could be done periodically, and wouldn't need all the workbooks to be constantly accessible via a network. For an inventory of bulk items, each users' workbook would require a macro that would update the quantities in the master workbook as items are moved into/out of store. It also means either setting up 'trusted locations' for these workbooks, or requiring users to enable macros whenever they open their inventory workbooks. This would only be practical if all the workbooks are constantly accessible via a network - otherwise, no one could be sure of what quantities were in stock at any time.

As you can see, we haven't even started with the coding yet - it's all a matter of getting the structure sorted out first. Mind you, once you get the inventory structure and management issues sorted out, you might also quite reasonably conclude that a database program like Access would be better suited to your needs - and you wouldn't need any macros.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Last edited by macropod; 02-02-2011 at 01:32 AM.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Unable to open Excel 97-2003 workbook J Partridge Excel 1 11-07-2010 03:26 AM
"Workbook not saved" message Lesq Excel 0 07-23-2010 01:59 AM
macro to transfer data from one workbook to another workbook How do I merge data from one sheet in a workbook out into multiple sheets nolesca Excel 4 06-07-2010 08:13 AM
macro to transfer data from one workbook to another workbook Select a range in one one workbook while working in other workbook Slow&Steady Excel 1 02-21-2010 03:34 AM
Share Workbook cannot merge Ichigo Excel 3 01-19-2010 03:05 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:52 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