VBA Copy Paste column to next blank column

[ferr]

I have a data tab populating values for months running from Jan- Dec in Columns B-M.
The current column is formula driven as it pulls values from a separate table/query in the spreadsheet that is filtered for the current month..
lets assume the current column is Column C (February)- “C2:C6” is pulling values from the other table.
When running the report for March (Column D), I will copy and paste the formulas from C to D, and then paste as values into C (as it will no longer be pulled from the filtered by month table in the spreadsheet)
I need a macro that will copy and paste the current column to the next blank column and then overwrite(paste as values) in the current column.
So if “C2:C6” is pasted in “D2:D6”, then paste as values back in “C2:C6”. Then the next time I run the macro, it will go from “D2:D6” to “E2:E6”.
But the range is Column B-M

In the screenshot attached, ignore the header 'weeks' and assume it is months (Jan-Dec)
I need a macro that works within the range Column B-M, copies “C2:C6”, pastes as formula in the next blank column i.e D2:D6, then paste as values in C2:C6...
Then the next time I run the macro, it copies from D2:D6, pastes in E2:E6 and then pastes as values in C2:C6.

 

[Joe4]

Welcome to the Board!

Assuming that there is nothing past column M in row 2, try this:

Sub MyCopyMacro()

    Dim c As Long
    
'   Find last populated column in row 2
    c = Cells(2, Columns.Count).End(xlToLeft).Column
    
'   If column > 1
    If c > 1 Then
'       Copy over formulas to next column
        Range(Cells(2, c), Cells(6, c)).Copy Cells(2, c + 1)
'       Change original column to values
        Range(Cells(2, c), Cells(6, c)).Value = Range(Cells(2, c), Cells(6, c)).Value
    Else
        MsgBox "Nothing past column A in row 2", vbOKOnly, "ERROR!"
    End If
            
End Sub

 

[ferr]

Thank you so much.
It worked once I removed stuff past column M (was in column O)
Is there a tweak in the code if I don’t want to remove what I have in column O?

 

[Joe4]

Try this variation:

Sub MyCopyMacro()

    Dim c As Long
    
'   Find last populated column in row 2
    c = Range("N2").End(xlToLeft).Column
    
'   If column > 1 and < 13
    If (c > 1) And (c < 13) Then
'       Copy over formulas to next column
        Range(Cells(2, c), Cells(6, c)).Copy Cells(2, c + 1)
'       Change original column to values
        Range(Cells(2, c), Cells(6, c)).Value = Range(Cells(2, c), Cells(6, c)).Value
    Else
        MsgBox "Nothing past column A in row 2 or data already in column M", vbOKOnly, "ERROR!"
    End If
            
End Sub

 

[ferr]

Try this variation:

Sub MyCopyMacro()

    Dim c As Long
   
'   Find last populated column in row 2
    c = Range("N2").End(xlToLeft).Column
   
'   If column > 1 and < 13
    If (c > 1) And (c < 13) Then
'       Copy over formulas to next column
        Range(Cells(2, c), Cells(6, c)).Copy Cells(2, c + 1)
'       Change original column to values
        Range(Cells(2, c), Cells(6, c)).Value = Range(Cells(2, c), Cells(6, c)).Value
    Else
        MsgBox "Nothing past column A in row 2 or data already in column M", vbOKOnly, "ERROR!"
    End If
           
End Sub

Thank you ! Works perfectly.

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[ferr]

You are welcome.
Glad I was able to help!

Is there a modification to the code so when we get to the last column M, it is able to repeat the sequence i.e copy and paste from M into B (assuming B is blank)?

 

[Joe4]

Just clear out the data in columns B:M and it should start over.