Macro to work on selected cell

[Barney]

I have a macro that performs a routine on a row of data. My spreadsheet has
many rows of data. How can I use only one macro that will work on any row
that I have selected so that I don't need a different macro for every row?

Thanks

Barney

 

[Don Guillett]

You were NOT very specific.

rows(activecell.row).copy >>>

 

[Gord Dibben]

With ActiveCell.Row
'do your stuff
End With

Should do the job.


Gord Dibben MS Excel MVP

 

[Barney]

Don and Gord,

Sorry to be so brief. I have a spreadsheet (Excel 2002) with fourty
rows. I want to copy and paste cells, in any selected row, from one
location to another. I don't want to have a macro for each row. One macro
with one button is my goal. Here is my macro for row 5:

Sub New1()
Range("G5:O5").Select
Selection.Copy
Range("H5").Select
ActiveSheet.Paste
Range("G5").Select
Selection.ClearContents
End Sub

Thanks for your help.

Barney

 

[Don Guillett]

I suspect you still weren't clear about what you want but this will MOVE g
of the activecell.row over one column.

Sub MoveRowData()
mr = ActiveCell.row
Range(Cells(mr, "g"), Cells(mr, "o")).Cut Cells(mr, "h")
End Sub

 

[Gord Dibben]

Barney

All you are doing is moving G5:O5 over one column.

Why don't you just select a cell or range of cells in column G and
Insert>Cells>Shift Right?

Anyway...................for activecell only.

Sub foo()
ActiveCell.Resize(1, 9).Cut _
Destination:=ActiveCell.Offset(0, 1)
End Sub


Gord

 

[Barney]

Don, Here is what I ended up with. Cut/paste changed my formula, which is
in cell "f", with the move while Copy/paste does not.

Sub MoveRowData()
mr = ActiveCell.Row
Range(Cells(mr, "g"), Cells(mr, "o")).Copy Cells(mr, "h")
Cells(mr, "g").Select
Selection.ClearContents
End Sub

Gord, I have not tried your last suggestion, but I will. Is it more
elegant, simple, faster?

Thanks to both of you for your help.

Barney

 

[Don Guillett]

Gord's idea recorded.
Sub Macro7()
'
' Macro7 Macro
' Macro recorded 5/19/2007 by Donald B. Guillett
'

'
Selection.Insert Shift:=xlToRight
End Sub