very simple macro needed

[kylefoley76]

i need a macro where it selects cells b to br, copies them, then goes down a row and pastes 4 rows. for example, if the selection was on b56, it would select b56:br56, then copy, then paste b57:br61. but i need the macro to be set up where it copies and pastes according to that one function, i forget its name, it's something where you specify the cells according to the cell you're on, like if you're at a1, then b2 would be [-1,-1]. thanks in advance.

 

[kylefoley76]

I just though of something else I need for this macro. After I copy and paste, let's say b56:br61. I then need to select b52:br56, copy them, paste values, then find zeroes and replace them with a blank. Thanks in advance.

 

[RSXchin]

So, you DON'T want it to always start on column B?

 

[WhatTheXL]

Try:

 Sub CopyRow4x()
   'You only need to be on the row when macro starts.
   Dim Rng As Range
   Set Rng = Selection
 
   Cells(Rng.Row, 2).Resize(, 69).Copy Destination:=Cells(Rng.Row + 1, 2).Resize(4)
 
   Set Rng = Nothing
 
End Sub

 

[WhatTheXL]

Try:

Sub CopyRow4x()
   'You only need to be on the row when macro starts.
   Dim RngFrom As Range
   Dim RngTo As Range
 
   Set RngFrom = Selection
   Set RngTo = Cells(RngFrom.Row + 1, 2).Resize(4, 69)
 
   Cells(RngFrom.Row, 2).Resize(, 69).Copy Destination:=RngTo
 
   With RngTo
      .Value = .Value
      .Activate
   End With
 
   Set RngFrom = Nothing
   Set RngTo = Nothing
End Sub

 

[kylefoley76]

thanks that did it. Any idea about the second half of the macro?

 

[WhatTheXL]

Try

Sub CopyRow4x()
   'You only need to be on the row when macro starts.
   Dim Cell As Range
   Dim RngFrom As Range
   Dim RngTo As Range
 
   Set RngFrom = Selection
   Set RngTo = Cells(RngFrom.Row + 1, 2).Resize(4, 69)
 
   Cells(RngFrom.Row, 2).Resize(, 69).Copy Destination:=RngTo
 
   For Each Cell In RngTo
      If Cell.Value = 0 Then
         Cell.Value = ""
      Else
         Cell.Value = Cell.Value
      End If
   Next Cell
 
   RngTo.Activate
 
   Set Cell = Nothing
   Set RngFrom = Nothing
   Set RngTo = Nothing
End Sub