Creating a Macro

L

Lynch

I am using Excel 2003 and would like to great a command
button the when clicked on it would take an entire row,
including embedded text forms and comboboxes, Etc..copy
it to 2 rows below it. and when clicked again it will
copy it 4 rows below it the 6,8,10 etc....This is my
first time working with macros....Thanks for the help...
 
P

Paul D

Lynch said:
I am using Excel 2003 and would like to great a command
button the when clicked on it would take an entire row,
including embedded text forms and comboboxes, Etc..copy
it to 2 rows below it. and when clicked again it will
copy it 4 rows below it the 6,8,10 etc....<clip>
Click to expand...

Based on the limited information you gave, here is one way.

Create a module with any name you like.
at the very top (below the words option explicit if you have that turned on)
type
Dim myRowOffset As Integer

This creates a variable that will not reset each time the routine is run.
If you need something to be sheet specific or stored for use to resume
counting in a future excel session, then I recommend saving this value in
the workbook rather than as a variable
now add the following routine below the dim statement you just typed

Sub CopyRow()
Dim OriginalCell As Range

Application.ScreenUpdating = False
Set OriginalCell = ActiveCell
If myRowOffset = 0 Then myRowOffset = 2
With [A1]' <-change the 1 to whatever row you want to copy each time
.EntireRow.Copy
.Offset(myRowOffset).Select
End With
ActiveSheet.Paste
OriginalCell.Select
myRowOffset = myRowOffset + 2
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Create a button and assign the macro CopyRow to it and you should be set.

Paul D
 
Top