 <p>Try this:</p> <pre><code>Sub Macro1(colindex As Integer) ' ' Macro1 Macro ' 'make our ranges variable and based on the input column Dim range_alpha As Range, range_beta As Range Set range_alpha = ActiveSheet.Range(ActiveSheet.Cells(18, colindex), ActiveSheet.Cells(20, colindex)) Set range_beta = ActiveSheet.Range(ActiveSheet.Cells(18, colindex + 1), ActiveSheet.Cells(20, colindex + 1)) range_alpha.Select range_alpha.Select Selection.Copy range_beta.Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False range_beta.Select Application.CutCopyMode = False Selection.Copy range_alpha.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Range("M18:M20").Select End Sub Sub Macro1_BySelection() '' use this sub to call Macro 1 if you want to select a column first Macro1 Selection.Column End Sub Sub Macro1_ByProbe() '' use this sub to call Macro 1 for the first empty column found Dim tst As String Dim colindex As Integer colindex = 1 tst = ActiveSheet.Cells(18, colindex).Value Do While tst &lt;&gt; "" colindex = colindex + 1 tst = ActiveSheet.Cells(18, colindex).Value Loop Macro1 colindex End Sub Sub Macro1_ByR3uk() Dim LastCol As Integer LastCol = ActiveSheet.Cells(18, ActiveSheet.Columns.Count).End(xlToLeft).Column Macro1 LastCol + 1 End Sub </code></pre> <p>Your initial function has been tweaked to act not on the fixed range of "Lxx" but to accept a number as column index.</p> <p>The two other macro's offer two distinct ways of determining which range to use:</p> <ul> <li><code>Macro1_BySelection()</code> uses the cell that is selected when the macro is called.</li> <li><code>Macro1_ByProbe()</code> tests to find the first empty column.</li> <li>EDIT: I liked R3uk's way of finding the last used column better than mine, used his technique for <code>macro1_ByR3uk()</code> (we learn every day :-) )</li> </ul>