Excel VBA - copy current month's column then insert in the next column to make new month

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,004
Office Version
  1. 365
Platform
  1. Windows
Hi folks,

Each month end, I create a copy of the last month's end file. as part of this process, all Report sheets (those that are sent out to the business) have the new month added to their tables. this is done by copying the previous month's (eg October) column, and inserting the copied cells in a new column adjacent to it. the previous month's formulas are then converted to values before the new month's raw data is imported.

I currently use this button fired routine to copy the whole column:
Code:
Private Sub CommandButton1_Click()
    Dim R As Range, BeforeR As Long

'Find 'Totals' in row 5 of active sheet
Set R = Rows(5).Find(what:="Totals", lookat:=xlWhole)

'identify the column to copy (last month)
BeforeR = R.Column - 1
    
    If R Is Nothing Then
      MsgBox ("The word 'Totals' was not found in Row 5 - macro terminated!")
        Exit Sub
    ElseIf Not R Is Nothing Then
    
    'copy last month's column
    Columns(BeforeR).Copy
    
    'insert copied cells before the Totals column
    Columns(R.Column).Insert Shift:=xlRight
    
    'change last month's column to values
        With Columns(BeforeR)
        .Value = .Value
        End With
 
    Application.CutCopyMode = False
    End If
    End Sub

On a separate sheet with a different layout, I want to do the same sort of thing but with only a range, rather than the whole column.

Code:
Private Sub CommandButton6_Click()
    Dim P As Range, BeforeP As Long, LstMth As Range
    
'Find 'Totals' in row 5 of active sheet
Set P = Rows(34).Find(what:="Totals", lookat:=xlWhole)
'MsgBox P.Address 'H34

'identify the column to copy (last month)
BeforeP = P.Column - 1
Set LstMth = ActiveSheet.Range(Cells(31, BeforeP), Cells(500, BeforeP)) '''copy G31:G500
'MsgBox BeforeP
    
    If P Is Nothing Then
      MsgBox ("The word 'Totals' was not found in Row 5 - macro terminated!")
        Exit Sub
    ElseIf Not P Is Nothing Then
    
    'copy last month's column
    LstMth.Copy
    
    'insert copied cells before the Totals column
    ActiveSheet.Cells(31, P.Column).Insert Shift:=xlRight  '''insert H31
    
    'change last month's column to values
        With LstMth
        .Value = .Value
        End With
 
    Application.CutCopyMode = False
    End If
    End Sub

This routine errors out at the insertion point. That is, at:
Code:
 'insert copied cells before the Totals column
    ActiveSheet.Cells(31, P.Column).Insert Shift:=xlRight

"Run time error 1004: Application defined or object defined error"

what syntax should i be using to get the current month range to copy into the adjacent range?
 
Last edited:

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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
Back
Top