Auto Data Transfer
Hi Guy's,
I have created an intergrated system of tracking for my company. Basically I have one Workbook for Daily Sales and another as a Master Record. I want to be able to input information into the Master Worksheet and automatically transfer some of the information to the Daily Worksheet. Columns A,B,H,J,K,P,Q,T,G on Master are to be transferred to Columns A,B,C,D,E,I,K,F,L respectively on a daily basis according to matching dates in Column S of the Master and Cell H1 of the Daily sheet. An added problem is that cell information can regularly be the same and there can be up to 20 different transactions per day. -- KRAMER |
Kramer,
Don't do it that way: it's a poor design, one that increases your chance of introducing errors. Use one workbook, and one database, and use data filters or pivot tables to view summaries or subsets of your data. You can add additional columns to your database to use as filter keys. When you want to see a specific view of your data, filter on the key, or other parameters, such as the 'matching dates' or the column that would match the value in cell H1. Or use the pivot table to create the summary. HTH, Bernie MS Excel MVP "KRAMER" wrote in message ... Hi Guy's, I have created an intergrated system of tracking for my company. Basically I have one Workbook for Daily Sales and another as a Master Record. I want to be able to input information into the Master Worksheet and automatically transfer some of the information to the Daily Worksheet. Columns A,B,H,J,K,P,Q,T,G on Master are to be transferred to Columns A,B,C,D,E,I,K,F,L respectively on a daily basis according to matching dates in Column S of the Master and Cell H1 of the Daily sheet. An added problem is that cell information can regularly be the same and there can be up to 20 different transactions per day. -- KRAMER |
Thanks Bernie,
The problem there is that I have to e-mail the Daily section every day and if I combine it with the Master section then I have over 8 MB even zipped it's still 4 MB which will not happen on dial-up (we're still in the stone age). I don't know what a pivot table is but if it can be formatted to match what I have now and can be e-mailed with ease than I will certainly learn what I can. Same goes with filtering. My only sources of training are the help menu and Excell 2003 Fast & Easy, so if there is anything else that is good for beginers, please tell. Thanks again. -- KRAMER "Bernie Deitrick" wrote: Kramer, Don't do it that way: it's a poor design, one that increases your chance of introducing errors. Use one workbook, and one database, and use data filters or pivot tables to view summaries or subsets of your data. You can add additional columns to your database to use as filter keys. When you want to see a specific view of your data, filter on the key, or other parameters, such as the 'matching dates' or the column that would match the value in cell H1. Or use the pivot table to create the summary. HTH, Bernie MS Excel MVP "KRAMER" wrote in message ... Hi Guy's, I have created an intergrated system of tracking for my company. Basically I have one Workbook for Daily Sales and another as a Master Record. I want to be able to input information into the Master Worksheet and automatically transfer some of the information to the Daily Worksheet. Columns A,B,H,J,K,P,Q,T,G on Master are to be transferred to Columns A,B,C,D,E,I,K,F,L respectively on a daily basis according to matching dates in Column S of the Master and Cell H1 of the Daily sheet. An added problem is that cell information can regularly be the same and there can be up to 20 different transactions per day. -- KRAMER |
Kramer,
You can easily extract data from a database. Suppose your database has a date column. Copy the macro below into a codemodule in your workbook. Then select a single cell in your date column with the date that you want to extract, and then run the macro. It will create a new sheet that contains just the data for the that single day. Then emailing that single sheet would be quicker. The assumption is that your database follows database rules - no fully empry columns or rows. HTH, Bernie MS Excel MVP Sub ExtractJustOneDay() Dim myCell As Range Dim mySht As Worksheet Set myCell = ActiveCell Set mySht = Worksheets.Add mySht.Name = Format(myCell.Value, "mmm dd, yyyy") With myCell.CurrentRegion Intersect(.Cells, myCell.EntireColumn).AutoFilter _ Field:=1, Criteria1:=myCell.Text .SpecialCells(xlCellTypeVisible).Copy _ mySht.Range("A1") .AutoFilter End With End Sub "KRAMER" wrote in message ... Thanks Bernie, The problem there is that I have to e-mail the Daily section every day and if I combine it with the Master section then I have over 8 MB even zipped it's still 4 MB which will not happen on dial-up (we're still in the stone age). I don't know what a pivot table is but if it can be formatted to match what I have now and can be e-mailed with ease than I will certainly learn what I can. Same goes with filtering. My only sources of training are the help menu and Excell 2003 Fast & Easy, so if there is anything else that is good for beginers, please tell. Thanks again. -- KRAMER "Bernie Deitrick" wrote: Kramer, Don't do it that way: it's a poor design, one that increases your chance of introducing errors. Use one workbook, and one database, and use data filters or pivot tables to view summaries or subsets of your data. You can add additional columns to your database to use as filter keys. When you want to see a specific view of your data, filter on the key, or other parameters, such as the 'matching dates' or the column that would match the value in cell H1. Or use the pivot table to create the summary. HTH, Bernie MS Excel MVP "KRAMER" wrote in message ... Hi Guy's, I have created an intergrated system of tracking for my company. Basically I have one Workbook for Daily Sales and another as a Master Record. I want to be able to input information into the Master Worksheet and automatically transfer some of the information to the Daily Worksheet. Columns A,B,H,J,K,P,Q,T,G on Master are to be transferred to Columns A,B,C,D,E,I,K,F,L respectively on a daily basis according to matching dates in Column S of the Master and Cell H1 of the Daily sheet. An added problem is that cell information can regularly be the same and there can be up to 20 different transactions per day. -- KRAMER |
All times are GMT +1. The time now is 03:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com