millbrookmom
08-17-2008, 01:17 PM
Hi everyone, I'm a mom, living in small town Ontario Canada, who dabbles with whatever software is needed to get the job done. My background is in office admin, so there's no degree on my wall. I've been using Office 2000 and Windows XP for quite a few year and I'm pretty comfortable using Excel. I also know that I don't use it anywhere near the capacity it can be.
Right now I'm working on computerizing the hot lunch program for the school, so I'm using Excel to get it done.
It might not be very sexy, but what I've done is create sheets with template-like forms, protected so they can only enter data in certain places. "Annual Form" "Annual Form TT" "December Form" "December Form TT" "March Form" "March Form TT"
This information is gathered by another sheet "database" by formulas in the first row of the table.
I need a macro to copy the information in the first row (where the formulas are, then paste the results as values in the next blank row of the table, effectively adding lunch orders to the database.
I really have no working knowledge of VBA. My only macro knowledge is recording keystrokes - which doesn't work for this.
I've attached the file, and since I don't know if the code will go with the attachment, I'll copy it below.
I appreciate any help that you can give me.... I've been searching the web for a week looking for information that I can understand... most of these sites seem to be meant for programmers (I'm NOT a programmer).:banghead:
One other thing that's going on.... the buttons that I have assigned the macros to won't work anymore, since I copied the files to a disk for the program co-ordinator. (I have another macro that clears the form info). I get a message saying that another copy of the file is open (when it's not) How can I fix that?
Thank you thank you thank you, for any help! This program needs to be up and running for the new school year!
regards,
Marjorie
Sub enterdata()
'
' enterdata Macro
' Macro recorded 13/08/2008 by Marjorie McDonald
'
' Keyboard Shortcut: Ctrl+d
'Copies the data entered in the first row as values in the next blank row
'
'
'
' go to database sheet
'start at the currently selected cell
Sheets("Database").Select
x = ActiveCell.Row
'copy and paste this row as values to the next blank row below
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Next Blank Cell Below
ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Loop
End Sub
Right now I'm working on computerizing the hot lunch program for the school, so I'm using Excel to get it done.
It might not be very sexy, but what I've done is create sheets with template-like forms, protected so they can only enter data in certain places. "Annual Form" "Annual Form TT" "December Form" "December Form TT" "March Form" "March Form TT"
This information is gathered by another sheet "database" by formulas in the first row of the table.
I need a macro to copy the information in the first row (where the formulas are, then paste the results as values in the next blank row of the table, effectively adding lunch orders to the database.
I really have no working knowledge of VBA. My only macro knowledge is recording keystrokes - which doesn't work for this.
I've attached the file, and since I don't know if the code will go with the attachment, I'll copy it below.
I appreciate any help that you can give me.... I've been searching the web for a week looking for information that I can understand... most of these sites seem to be meant for programmers (I'm NOT a programmer).:banghead:
One other thing that's going on.... the buttons that I have assigned the macros to won't work anymore, since I copied the files to a disk for the program co-ordinator. (I have another macro that clears the form info). I get a message saying that another copy of the file is open (when it's not) How can I fix that?
Thank you thank you thank you, for any help! This program needs to be up and running for the new school year!
regards,
Marjorie
Sub enterdata()
'
' enterdata Macro
' Macro recorded 13/08/2008 by Marjorie McDonald
'
' Keyboard Shortcut: Ctrl+d
'Copies the data entered in the first row as values in the next blank row
'
'
'
' go to database sheet
'start at the currently selected cell
Sheets("Database").Select
x = ActiveCell.Row
'copy and paste this row as values to the next blank row below
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Next Blank Cell Below
ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Loop
End Sub