Automating transfer of data in cells

I have a time management spreadsheet with data stored 
against work type and date.  I need to transfer this data 
into a similar but more comprehensive spreadsheet and 
wonder whether it is possible to automate this task by 
using the work types and dates in a macro (I have almost 
10 months of data to transfer), along the lines of check 
date, check worktype, where argument is true enter data 
from cell.  I think I need to use visual basic, but I 
can't find out how in the help screens.

Any advice is much appreciated.  
0
anonymous (74722)
1/21/2004 10:22:54 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
263 Views

Similar Articles

[PageSpeed] 48

This is not difficult providing you keep your data in simple tables and
put them all into a single master. No need for formatting or anything
else because you can then use the more powerful features of Excel such
as Pivot tables and Database Formulas to do analyses.

Here is a suitable base macro that you might find helpful. It would be
fairly straightforward to filter the data a bit before transfer, but,
in my opinion, a waste of time. It is usually easier to do all this in
the final table - or simply omit it from your calculations.

'===============================================
'- Generic code for transferring data from
'- one or more workbooks to a master sheet
'-
'- workbooks must be the only ones in the folder
'- worksheets must be the first one in the book
'- worksheets must be contain tables which are
'- identical to the master, headings in row 1.
'- master sheet is remade each time.
'- run this code from the master book
'-
'----------------------------------------------
Dim ToBook As String
Dim ToSheet As Worksheet
Dim NumColumns As Integer
Dim ToRow As Long
Dim FromBook As String
Dim FromSheet As Worksheet
Dim FromRow As Long
Dim LastRow As Long
'-
'----------------
Sub NEW_MASTER()
'----------------
Application.Calculation = xlCalculationManual
ChDrive ActiveWorkbook.Path
ChDir ActiveWorkbook.Path
ToBook = ActiveWorkbook.Name
Set ToSheet = ActiveWorkbook.Worksheets(1)
NumColumns = ToSheet.Range("A1").End(xlToRight).Column
ToRow = ToSheet.Range("A65536").End(xlUp).Row
'- clear master
If ToRow <> 1 Then
ToSheet.Range(Cells(2, 1), Cells(ToRow,
NumColumns)).ClearContents
End If
ToRow = 2
'- main loop
FromBook = Dir("*.xls")
While FromBook <> ""
If FromBook <> ToBook Then
Application.StatusBar = FromBook
Transfer_data
End If
FromBook = Dir
Wend
'-- close
MsgBox ("Done.")
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
End Sub
'-------------------------------------------------

Sub Transfer_data()
Workbooks.Open FileName:=FromBook
Set FromSheet = Workbooks(FromBook).Worksheets(1)
LastRow = FromSheet.Range("A65536").End(xlUp).Row
FromSheet.Range(Cells(2, 1), Cells(LastRow, NumColumns)).Copy _
Destination:=ToSheet.Range("A" & ToRow)
ToRow = ToSheet.Range("A65536").End(xlUp).Row + 1
Workbooks(FromBook).Close savechanges:=False
End Sub
'==== EOP ======================================


---
Message posted from http://www.ExcelForum.com/

0
1/22/2004 1:32:40 PM
Reply:

Similar Artilces:

Cant transfer from a current account to loan acccount
Hi I am unable to "transfer" from a current account to a loan account. From the current account, I select 'Tranfer', then from the 'To' box, I am unable to find my loan account in that list. Please can you advise the method of paying down a load account from a current account? Thank you in advance. Use Loan Payment and delete the Interest portion in the splits. Set the Principal Transfer amount to the amount you want to transfer. Assumes not Money Essentials (MEss) not Essential Register. "April" <noreply@mail.com> wrote in message news:u...

How to replace PivotTable data fields...?
Hi, I'm progamming a pivot table using VBA. I have no problem manipulating column or row fields, but how can I replace a data field? Everything I try just adds the data field to the existing data field resulting in a mess. The PivotTable.addfields methods *replaces* whatever columns were previously designated as RowFields, ColumnFields, and PageFields with the new specifications, as in: ..AddFields RowFields:="Date", PageFields:="Location" But no matter what I try, I can't replace the data field.... I've even tried setting the Orientation property to xlHidden...

access of oracle9i data base with MFC
i m getting some problem while accessing the data base of oracle9i . whenever i m accessing the data base it is always asking password and name , i need to remove this so what should u include in my code that it will privent the same Please, add more details about your problem. It's difficult for us to guess how you are connecting to Oracle database since there are a lot of ways to do it. Jaime "hame" <hame@discussions.microsoft.com> wrote in message news:43299876-F05F-417E-84F0-F51A5A9AF3AC@microsoft.com... > i m getting some problem while accessing the dat...

Uniquely Identify Data for Charting with a List Box
Gang, The Internet is a great thing. I found a charting example that does nearly every thing I want to do. Amazingly enough, I was able to figure out how the thing works! The chart is fairly simple. It is a X*Y line chart with 2 series. I found the chart here: http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - The chart is called "ChartAgainstStandard.zip." One of the 2 series plotted is a "standard" and the other changes based on the selection made in the drop down list. Amazingly enough, I was able to follow the thing completely and I would like ...

Multiple choices for a cell
I am creating a worksheet which deals with employee or job candidates qualifications. Each cell will hold the various details pertaining to a person's qualifications. Different people have different degrees. This will be all stored in a single cell and separated by a comma. Is it possible to create some sort of custom or drop down box which contains controls or check boxes so as to create a standardised list of qualifications the data entry clerk can choose from? For example, i would like a cell to have a drop down box, which is a field that displays the qualifications a particular gra...

help with spliting data cells
I am looking for assistance with splitting data between cells. I have a column of data (first (space) last name). I need to split that column into two seperate columns so each name value is in it;s own cell and i end with 2 seperate columns. (john smith) to (john) (smith) Please advise if this is easy and can be done. Thanks, Phil pcavalcanto@Aol.com -- pcavalcanto ------------------------------------------------------------------------ pcavalcanto's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32212 View this thread: http://www.excelforum.com/showthread...

Select range for function in a cell
Hi, is it possible to select create a formula in a cell. such as =SUM( and have the cursor be between the () so the user can select th appopriate range to enter? Or would this just be done separately. (Since one would need to know the range and WHERE to put the formul in..ie. what cell) similar to if you hit the autosum button but ther are no numbers above or the left, it simply waits for the user t select a range and then hits enter. THanks! -- Message posted from http://www.ExcelForum.com Hi if I understood you correctly: No -- Regards Frank Kabel Frankfurt, Germany > Hi, is it p...

Copy and Paste in Excel, copies cell and formula, but shows same v
Excel 2003 with 2007 converters installed. Not constant problem, but have seen it once before, can't remember how to stop it happening. I have a bank forecast which I have a running total column, against a individual line item list. Noddy stuff. Problem. If I make changes to the forecast I need to recopy down the running total formula to refresh the running total when it doesn't pick up the additions, usual when a cut has been pasted in. Whilst it allows me to dragdown or copy and paste the formula and the new cells take on the correct line/column numbers to do thei...

How do I create a graph from an equation and not a set of data?
How do I create a graph from an equation and not a set of data? Hi, Stephen Bullen has an example. See ChtFrmla within the Charting section. http://www.oaltd.co.uk/Excel/Default.htm The example file is, http://www.oaltd.co.uk/DLCount/DLCount.asp?file=ChtFrmla.zip Also see Tushar Mehta's Plot manager, http://tushar-mehta.com/excel/software/plot_manager/index.html Cheers Andy ashemorry wrote: > How do I create a graph from an equation and not a set of data? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

How to wipe out HQ and SO data without the setting and paramters
Hi. Im planning to wipe out my database without clearing out its parameters and settings. I have an existing Store running on HQ and SO. I plan to change the barcodes for all items as they are inconsistent. Therefore i need to wipe out everything without the parameters and configuration settings. Can This be done? If so i need a detailed steps in doing this. This sounds very dangerous. What if you changed all your items so they start with a 'Z-' and then make all those items InActive, that way will not lose any Sales History? Then delete everything from the Alias table if you ar...

displaying a comment only when the cell is selected #2
I'd like to insert a comment in a certain cell, so that when that cell is selected by a user, the comment is then displayed. I know this must be possible, but I haven't been able to figure it out.... I can show or hide comments in various combinations, but can't find a way to hide the comment UNTIL the cell is selected, and then display it. How do I do this please?! Thanks Hi Jeff, Try: Tools Options | View tab | select 'Comment indicator only" | OK --- Regards, Norman "Jeff" <no_em@ilplea.se> wrote in message news:JdidnecOi7ehGVbfRVnyhg@eclip...

Cannot edit data in forms but can in tables
I have a problem! I created a database where I can only edit the data in tables. In the forms I created I can see the data but cannot edit the data. I am using 2007 that I have set so the file can be opened in 2003. The data was imported to create tables from two Excel spread sheets using the wizard. The tables are linked together and the combined data is shown in a report. This all works without a problem. Now I need to edit the data, so I created several forms all of which I cannot add, delete or edit the data. I saw under the User and Group Permissions that u...

Last Cell #2
I know this has been asked before but I cant find what I want on Google. I need to get the address or row number of the first empty cell in column A, either will do, I need to use VB to copy and paste data from ten sheets into one. A formula or a VB way would be great, anyway of identifying the first empty cell. This cell may be empty or contain a null string. Any help would be reaaly good. TIA Alan. to find the last row x=cells(rows.count,"a").end(xlup).row+1 to find the next empty x=cells(1,1).end(xldown).row ' or address -- Don Guillett SalesAid Software donaldb@281.com &...

Automated changes of Outlook contact fields
What software / utilities / code etc. are available that would allow me to adjust some existing data in my Outlook 2003 contacts? For example, I have many "web page address" fields that need to be changed from e.g. http://subway.com to http://www.subway.com and also many "city" fields that need to be capitalized e.g los angeles to Los Angeles. Thanks in advance for any replies, Miner2049er. After some research, I believe I have to write a Visual Basic script to do this. Not knowing that language, does anybody know of a script that can do what I'm asking, or at l...

duplicate cells linearly?
Hello all - Is there a way to duplicate a formula in cells were the row reference grows linearly, instead of simply the number of cells? For instance, if you have in Sheet2 the formula Sheet1!$c1 and you duplicate it into rows below, you get Sheet1!$c2 Sheet1!$c3 Sheet1!$c4 Sheet1!$c5 However, we would like to do : Sheet1!$c4 Sheet1!$c8 Sheet1!$c12 We have a spreadsheet composed of two worksheets. In the first worksheet there are cells which are summed every 72 rows. We want those summed values to appear in worksheet 2, in one row after the other. Since we have a lot of summed cells, i...

Excel data query goes away.
I have a work book that gets information from an SQL database. About every two months the query goes away. Why? and how can I stop this from happening? ...

Cell Not working
I have office 2002, my windows is home xp. Last week we had to reformat my xp, got a hold of a nasty worm! I just use a chart to display numbers in series. Now when I try it from an old sheet it inserts the number as a date? I keep trying to change the cell to GENERAL, but it dosen't work. Can't understand it. When you say you keep trying to re-format the cell as General, what result do you get? If I do it on a date cell (re-format) I get the date code (i.e. the value of the date). Ex. cells has "03-01-11", select cell and re-format to General = 37632. -- Regards; Ro...

insert data from one excel file into another
Is there any way to insert data from one excel file into another without doing copy and pasting? I need to automate the process of inserting data but I cannot use copy-paste, because it puts data on a clipboard, and my Excel VBA program runs in a multi-user environment. So if one instance of a program is copying data into clipboard, and another instance is pasting it at the same time, it will paste incorrect data. Thank you Leonard. You'll need both spreadsheets open, but try this; Option Explicit Sub MyMacro() Dim MyVariable As String Windows("File2.xls").Ac...

delete row if given cell = 'X'
Is there an easy way to do the above. Thanks much Carl "MSNews" <nf@no.com> wrote in message news:%23Fj6fBvPKHA.764@TK2MSFTNGP02.phx.gbl... > Is there an easy way to do the above. > > Thanks much > > Carl > I'm not an expert but use data filter to select the rows, select and delete them. Try it with a trial sheet first though! Peter Several ways 1. data>filter>autofilter>filter on the x>delete 2. a looping macro from the bottom up sub delifx() for i=cells(rows.count,"a").end(xlup).row to 2 step -1 if lcase(cells(i,&qu...

How can I import my data from Entourage for Macosx
I a moving from a Apple Mac computer and I want to move all my data from Entourage to Outlook Ken Allen <Ken Allen@discussions.microsoft.com> wrote: > I a moving from a Apple Mac computer and I want to move all my data > from Entourage to Outlook See if this helps: http://www.entourage.mvps.org/cross_platform/ -- Brian Tillman "Ken Allen" wrote: > I a moving from a Apple Mac computer and I want to move all my data from > Entourage to Outlook. Will these scripts move all my e-mails and attachments over to Outlook as well. Thank you for all your help. I h...

using two cells 1 with total time n 1 with date how can i put the.
I'm trying to put the amount of time spent in to a seperate sheet (Cell # 2 column E ) from a sheet that has the dates in one cell (column A) and the time spent in another (column K) and the reason for the time spent in column B. On the seperate sheet I have to put the amount of time spent in column E and match with the date I have in column A on the seperate sheet to populate the chart that I'm making. The only thing that I have been able to do is create a formula to show the amount of time spent and for what reason but i can't match it to the date because the dates ...

removing 0 value data labels
Hi All How would I remove 0 Value data labels from a chart using vb script? No need for VBA. You can make a custom number format for the labels. Select the whole set of labels, press CTRL+1 to format, and on the number format, select Custom in the left hand list, and add a format like one of these: 0;0;;@ 0.0;0.0;;@ There are four items in a custom format, by default the formats for positive, negative, and zero values, and for text. Leave off the format for zeros, and the label will not appear. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and C...

Data migration manager error
Hi, I am running migration manager for accounts, I am getting error on importing the file that - data migration manager cannot run furter, close and try again. I have tried several times , it is not functioning.Is there any service to be started. regards Ritesh ...

Cannot access or restore my Money2005 data
I have backed up to a memory stick religiously. Nevertheless, I keep getting a popup message: "Exception Processing message c000013 Parameters 75b6bf9c 4 75b6bf9c 75b6bf9c" Any help or ideas would be greatly appreciated. Many additional data points are necessary to help. Examples: What version/edition of Money? What O/S? Under what conditions (what sequence of steps?) do you get this message? What happened (Internet Update, OS patch installation, Money reinstallation) when it started presenting itself? Do you get the same message trying the same sequence of steps in a new da...

Pivot Table
I have 2 lists of 100 items (rows) with 5 data elements. Each list has a common element, which can tie the two lists together. How can I combine these 2 lists and use it for the source data of a pivot table? Todd ...