Solved: copy and paste macro - simple, right?

[millbrookmom]

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).

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

[VBA] 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[/VBA]

[jproffer]

How about:

[vba]Sub Find_MT_Row
Range("A:A").Select
1 ActiveCell.Offset(1, 0).Rows.Select
If ActiveCell.row.Value = "" Then
Activesheet.paste
application.cutcopymode=false
Else: GoTo 1
End If
End Sub[/vba]

That is only the paste portion of the code, naturally...but see if that works. If not, we'll try again.

**this post has been edited, I followed your code after a second, sorry **

[millbrookmom]

Yes, you're right, I want it to copy from the first row and paste values to the next empty row. I'll take your word on what my code actually says

[mdmackillop]

I'm not clear whether you want only one row or the block of top rows copied. The following macro includes both options
[vba]Sub enterdata()

'Copy row containing activecell
With Sheets("Database")
.Cells(ActiveCell.Row, 1).Resize(, 75).Copy
.Cells(380, 1).End(xlUp).Offset(1).PasteSpecial xlValues
End With

'Copy rows 3 to 10
With Sheets("Database")
.Range("A3:A10").Resize(, 75).Copy
.Cells(380, 1).End(xlUp).Offset(1).PasteSpecial xlValues
End With
End Sub

[/vba]

[millbrookmom]

The first bit of code worked great! Can you add something to make the active cell the first row? When I run the code again, it copies from the last row, not the first row.

[millbrookmom]

This produced a Compile Error; Invalid Qualifier at If ActiveCell.Row.Value = "" Then

I had more luck with mdmackillop's suggestion

[millbrookmom]

hmmm, I think I may be confusing people with the order of my posts..... I assumed my response would go below the appropriate post. So to clarify, this is the code that worked so far. However subsequent runs copy for the last row instead of the first row.
[VBA]Sub enterdata()

'Copy row containing activecell
With Sheets("Database")
.Cells(ActiveCell.Row, 1).Resize(, 75).Copy
.Cells(380, 1).End(xlUp).Offset(1).PasteSpecial xlValues
End With
End Sub[/VBA]

[millbrookmom]

I've discovered that when I run the code from a different sheet, "Annual Form" for e.g., the copy comes from where ever the active cell is. i.e. if the active cell is in row 8 in another sheet, the macro runs from row 8 in "database" too. The first row in "database' is 13, and that's where the copy command always should be from. Am I making sense?

[mdmackillop]

Hi Mom!
You can only have an ActiveCell on the Active Sheet. If you want to be able to run ther code from anywhere, we need to have a way of determining the row you wish to copy. Is it always row 3 on Database? If not, how is it defined?

[millbrookmom]

Someone else to call me 'mom' - how wonderful! It's blatantly obvious to me that I know nothing whatsoever about vba. I appreciate your patience with my primitive attempts. To answer your question, the row is always 13 on the sheet "database", and the macro would be run from any one of the other data entry sheets.

[mdmackillop]

Now I see what you are after
[VBA]
Sub dataentry()
With Sheets("Database")
.Range("A13").Resize(, 75).Copy
.Cells(380, 1).End(xlUp).Offset(1).PasteSpecial xlValues
End With
End Sub

[/VBA]

[millbrookmom]

Hurray! It works just like I wanted it to. You have my undying gratitude! This has certainly helped me to get this project done on time. I'll be sure to remember where to come when I need help in the future. (Maybe I'll pick up a book too, so I have at least a clue what's going on)

[mdmackillop]

Happy to help. I'm sure there is a lot more that can be done to make things easier. Look up Data Validation in Help, also here, (no VBA involved!) Any questions, let us know.