VBA Copy Paste column to next blank column

ferr

New Member
Joined
Jun 9, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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

1654792078506.png


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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,098
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Assuming that there is nothing past column M in row 2, try this:
VBA Code:
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

New Member
Joined
Jun 9, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,098
Office Version
  1. 365
Platform
  1. Windows
Try this variation:
VBA Code:
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
 
Solution

ferr

New Member
Joined
Jun 9, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Try this variation:
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,098
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad I was able to help!
 

ferr

New Member
Joined
Jun 9, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,098
Office Version
  1. 365
Platform
  1. Windows
Just clear out the data in columns B:M and it should start over.
 

Forum statistics

Threads
1,181,436
Messages
5,929,875
Members
436,700
Latest member
lewismorg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top