Macro to work on selected cell

  • Thread starter Thread starter Barney
  • Start date Start date
B

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
 
With ActiveCell.Row
'do your stuff
End With

Should do the job.


Gord Dibben MS Excel MVP
 
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
 
I suspect you still weren't clear about what you want but this will MOVE g:o
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
 
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
 
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
 
Gord's idea recorded.
Sub Macro7()
'
' Macro7 Macro
' Macro recorded 5/19/2007 by Donald B. Guillett
'

'
Selection.Insert Shift:=xlToRight
End Sub
 
Back
Top