I am looking for the simplest way to do the following:
Excel file one has 10 columns which has information added daily.
Excel File 2 needs to be a simplified report of file 1 - only containing 5 of those columns.
Either have file 2 be continually updated with the information entered in file 1 or have some kind of one click solution that when work is done on file 1 all new entries will be transferred to file 2.
Any ideas would be appreciated so much.
You could use the Worksheet_Change event on file 1 to automatically update file 2 - or, you could just link the relevant fields!
Last edited by xladept; 12-02-2012 at 09:00 PM.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
this sounds fairly do-able. biut without seeing what the files look like that you are working with, I can only offer suggestions that you will have to adjust to suite.
You did not mention what type of data you will be extracting into file 2 (totals, single value, min/max etc)
you can do a simple reference from 2 to 1, or a vlookup or a hmmm i could go on and on, but i will let you guide me as to how and what you want?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks for trying to help me out.
The data in the first file is simple accounting information
Similar to this format:
Date | Type | Amount | Value | Amount*Value | Balance | Total etc.
But in file 2 i simply need (as an example)
Date | Balance | Total
The problem is File 2 has to be forwarded to a different department so if i reference information from file to file 2 don't they need file 1 as well ? (they cant "handle" all the information in file 1 - that's why i have to find a quicker solution than trying to copy everything manually on a daily basis)
if file1 is on a server (or anywhere) they would be able to reference/open it, then file2 should still be able to read the data from file1.
if its not a file they woould be able to access, then perhaps you could still reference file 1 as suggested, but then, before forwarding file2, value all the formulas.
now wait before you freak out lol. this can be done with a very simple macro that you could create yourself using the macro record feature.
practice doing what you want a few times, so you know exactly what you want to do, then do it all 1 last time and let the record feature record everything you do. you can even set up a macro button that will use what you just created and do all you're work with just one click
Thanks for the suggestion.
I did exactly that on a practice workbook i created real quick (i don't have the actual one from work) and what happens when it creates the new file and copies everything i need, it doesn't seem to copy cell properties like width, formatting etc.
Am i doing something wrong ?
Are you using the copy method?? - If so, it should paste formats along with values??
I tried the macro recorder
Start recording.
Marked the cells i want transferred to the new workbook, copy, open new workbook, paste, save
End recording
Here is the code( i am sorry my vba knowledge is limited at best)
Range("A3:A22,C3:C22,F3:F22").Select
Range("F3").Activate
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="C:\Users\...\Desktop\Report.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
I just rechecked and played with some additional formatting. I guess what it doesnt copy is the cell width/ column width - is there a way to do this ?
Last edited by Sarkast; 12-04-2012 at 06:02 PM.
You could try this
Please Login or Register to view this content.
Last edited by Epscan; 12-04-2012 at 06:52 PM.
Thank you so much for taking the time to help me out.
I'm going to try all this on the actual file at work tomorrow and see if it works as planned.
Finally what would be the best/ most elegant yet simple way of dealing with the growing amount of data ? In my test file used above i just copied ~20 rows and 3 columns. In the actual one the number of rows grows daily as data is added so a fixed range isn't really an option unless i copy every cell down to some really large number (A3:A9999) that i expect not to be reached any time soon. But this seems... clumsy.
Ideas ?
The best way is to set up your data is as arrays in core - What test file? It seems like you're only pasting one cell??? anon
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks