I know there must be away. I am getting decently good with excel while not using VB Code.
I have a daily log for work that keeps track of purchases and returns among other items and I was wondering if there was a way I could have all this information get put into a log that will amend everything for each week, month and year.
Do I have to use Access for that?
Hello ,
Welcome to the Forum!
Does this mean you are looking for a formulaic solution?
I know there must be some data. Can you provide some samples or even the workbook?I know there must be away. I am getting decently good with excel while not using VB Code.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
I think you need to explain more fully what you are hoping todo and maybe provide a sample workbook containing your layout but with dummy data in it
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
Sorry folks.
Here is the dummy sheet. Some of the colors are way off from the original since I had to convert it from 2007 to 2003 Excel to open at home.
UP SHEET.XLS
You will see how it keeps track of everything. All the dollar amounts I would like to keep track of in a separate document possibly called "StoreLog.xls" that always amends and adds up and saves everything that is entered in spreadsheet "Up Sheet.xls". I am guessing there is a way of doing this, maybe not with "Simple" code but at least something I can be taught and learn.
I have to go to work so I will talk more a little later.
If you have one database for all the daily records then you should be able to use PivotTable(s) to run reports on the data, see
http://www.excel-it.com/pivot_tables.htm
Creating "one" database with all the daily records is what I want to achieve. Right now that spreadsheet is for "One Day" only and it does not get saved, it gets filled out and then printed to be put into a file folder. The next day you open up the UP SHEET and start over and repeat the process. If I were to amend the UP SHEET daily and save all the information, I would have to change the "Print Area" to where I want it to print.
Is there any way I can have the information be copied and amended, saved to another sheet/database? Maybe have a button inserted into the spread sheet that saves "Amend" or "Save" so before you close out you click the button and have all the information be sent to the other LARGE database.
There's basic code to do this in the Contacts Form example here
http://www.excel-it.com/vba_examples.htm
Last edited by royUK; 01-02-2010 at 04:46 AM.
hmmmm. Thanks a lot! I think I found an example on that site that is similar to what I need to do. After I look at it more, I may need help implementing it into my sheet. I'll do my best by my self
Thanks!
Post back for further assistance
Ok, I am a little lost when looking at the VB Code. I have never touched VB code before. So how did you make the "Save" button transfer the data to those specific sells on the next sheet?
I guess it not as easy as say A2, A4, G7 and so on. Looks like the cells might be explained as (2, 3) and (0, 1)??
Please Login or Register to view this content.
Last edited by royUK; 01-02-2010 at 10:37 AM. Reason: add code tags
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
Added this time
In my code I use Cells(1,1) to refer to A!, you can use Range("A1") as well. So
A2 is Cells(2,1), A4 is Cells(4,1), G7 is Cells(7,7) etc
I See. I am starting to see how this works now.
If I wanted to copy Multiple rows and columns at once, how would I do that?
Sorry about the code. I did put them in a tag [code] [/ code] but it didn't work, maybe I typed it wrong but I thought the forum didn't support the code tag.
Looks like I make A LOT more of these?
Please Login or Register to view this content.
Can you attach an example workbook
So I edited some of the code and copied it into my workbook. I try to run the code but it says "Compilation Error. Variable Not Defined" and it highlights "Sub Save_Data()"
What do I need to change other than
andPlease Login or Register to view this content.
I REALLY appreciate your help in a big way!Please Login or Register to view this content.
Basically...."for now" till I really get the hang of it, I am just performing simple tasks such as copying information from the worksheet "UpSheet" to the worksheet "DataBase"
Here is the updated workbook and the code listed below
UpSheet.xls
Please Login or Register to view this content.
Last edited by jgray152; 01-02-2010 at 11:34 AM.
Try changing these lines as shown:
Please Login or Register to view this content.
Entia non sunt multiplicanda sine necessitate
Looking at your workbook it seems that you can move blocks of data from UPsheet to the database. I've amended the code to do this
Thanks Bud!
Now do you have a write up or know of a website or maybe you could explain what each part of the code does in more detail?? I sorta understand portions of it but not all of it. Unless there is a book on excel VB Code I can buy that would help me out?
I was looking at the code and I can see where you reference cell B2 but I don't know how it knows what information to copy. I am very new to VB Code so it may be better to get a book on it. Recommend one? Since there is more I want to do but I wouldn't want you to do everything for me
Is the VB Code in Excel different than any other type of VB Code?
I found this website that I will keep in mind for later.
http://pubs.logicalexpressions.com/p...cle.asp?ID=302
Seems like the guy has typed up a nice write up on VBA (Visual Basic for Applications) I just learned its called.
I added a cell in the "DataBase" worksheet for the date. Now I have the date being copied from another cell into the DataBase worksheet but it only adds it once regardless of how many rows are filled in. So when I click "Save Data" again, it will copy the data but it will start filling in just under the date in the "DataBase" worksheet and over write the existing data.
If I want Excel to look for the next blank row but I want it to look in say column B and not Column A (Where the date is listed), how would I do that?
Updated Example
UpSheet2.xls
Last edited by jgray152; 01-02-2010 at 09:13 PM.
I have put notes into the code, if you are not sure of terms in the code then try highlighting a specific word, e.g. CurrentRegion, then press F1 & any relevant entry in the VBA Help Files will open.
The code that I posted yesterday copies the input data to the main database, there's no dates to copy.I have amended it to copy the dtae & adjusted the range that it copies.
I'm not sure whether it is a good idea toget the date in the Input Sheet using a volatile formula like NOW() or TODAY() because if the data is copied on adifferent day to input date then the date will be updated by Excel & not be the date of input, but the date saved to database. Better to input the date manually.
Please Login or Register to view this content.
Ya I figured that out that the date will change in the copied database last night after fooling around with it. So I remembered a post from another member in another thread with this code that is supposed to produce a static date..
I just don't know how to target a specific cell. Maybe change where it saysPlease Login or Register to view this content.
??Please Login or Register to view this content.
For one cell it would be simpler to just manually input the date into the input sheet, if you want a formula fora static date then search the forum for a recent post that I made to do this. The code that I posted for you this morning willonly require the date entry on the input sheet instead of using NOW()
Ok so the code you made does work and it makes a static date. At least it keep the olrder date and does not change it. The only thing is that the first time I "click to save the data" it copies the date into 300 or so rows.. I looked at what I could understand and didn't see why it did this...?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks