Software

Question

Gravatar
0 Votes
Locked

Excel 200 VBA code needed!

By mominmillbrook ·
My macro knowledge is limited to recording keystrokes. I need a macro that will copy the first line of a table to the next blank line in the table as values. Sounds simple enough, but needs code to be written, as it won't work with a recorded macro. I feel like I'm almost there, but without any code/syntax knowledge, I could also be miles away from the answer. (I have copied bits and pieces from spreadsheetpage.com, but he doesn't explain simple syntax enough.)
The other problem I'm having is that the buttons I have attached the macros to won't work since I've saved the file to a disk. I get a message saying that another file with the same name is already open.(which is not the case) How can I fix this? The macro will run in the VBA editor.

here is what I've got so far: (I get a syntax error at the line that should copy and paste to the blank line below

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(ActiveCell, ActiveCell.End(xlToRight)).Select.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

Thanks so much for any suggestions!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

gravatar
Collapse -

This link might help with your Excel 200 VBA code..

by Peconet Tietokoneet-217038187993258194678069903632 In reply to Excel 200 VBA code needed ...

http://www.ozgrid.com/VBA/

Please post back if you have any more problems or questions.

gravatar
Collapse -

Thanks for the link

by mominmillbrook In reply to This link might help with ...

Thanks for the link to additional information. I should note that I missed a "0" in my subject line. I'm using Excel 2000, not 2007. I've done enough reading now to realize there is a difference in VBA from 2000 - 2007.

I will try to wade my way through the site, but I'll be honest, most of it I don't understand

gravatar
Collapse -

Will this work for you?

by ThumbsUp2 In reply to Excel 200 VBA code needed ...

You've got several syntax and logic errors there. See if this will work for you. It's not pretty. It leaves several things to chance. But, without knowing what your data looks like, it's hard to make up the differences for you. At least this will give you a place to start. I gave up before I figured out how to deselect the original selection. Maybe someone else can step in and give you that command.

[pre]
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
'
' initialize variables
Dim X As Integer, I As Integer, K As String
' X holds the row number
' I is used for looping
' K holds the starting cell address

' go to database sheet
' start at the currently selected cell
Sheets("Database").Select
X = ActiveCell.Row
K = ActiveCell.Address

' copy this row
' NOTE: Assume the cursor has been placed in the first
' cell of the row we want to copy
Range(ActiveCell, ActiveCell.End(xlToRight)).Select
Selection.Copy

' find next blank row below selection
I = 0
Do While ActiveCell.Offset(I, 0) <> ""
I = I + 1
Loop

' activate first cell in first blank row
ActiveCell.Offset(I, 0).Activate

' check to see if it is indeed empty,
' then do your paste special
If IsEmpty(ActiveCell) Then
Selection.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End If

' reset to the row below where we started
Range(K).Activate
ActiveCell.Offset(1, 0).Activate

End Sub
[/pre]

<edit: darned forum messes up the indentation, forgot the PRE tags>

Back to Software Forum

Start or search

Related Discussions

Related Forums