Excel Forum

Code: Select all

Sub CopyData()
    Dim month As Date
    Dim colHeader As Range, currMonthCol As Range, prevMonthCol As Range, formulaCol As Range, valuesCol As Range
    
    ' Get the month selected in S2
    month = Range("S2").Value
    
    ' Look for the column with matching header for the previous month
    Set colHeader = Range("F5:Q5").Find(what:=Format(DateSerial(Year(month), Month(month) - 1, 1), "mmm-yy"), LookIn:=xlValues, LookAt:=xlWhole)
    
    If Not colHeader Is Nothing Then
        ' Set the range for the current month column
        Set currMonthCol = colHeader.Offset(1, 1).Resize(colHeader.End(xlDown).Row - colHeader.Row + 1, 1)
        
        ' Set the range for the previous month column
        Set prevMonthCol = colHeader.Offset(1, 0).Resize(currMonthCol.Rows.Count, 1)
        
        ' Copy and paste formula from previous month column to current month column
        prevMonthCol.Copy
        currMonthCol.Offset(0, -1).PasteSpecial xlPasteFormulas
        
        ' Copy and paste values from previous month column to current month column
        prevMonthCol.Copy
        currMonthCol.Offset(0, -1).PasteSpecial xlPasteValues
        
        Application.CutCopyMode = False ' Clear clipboard
        
    Else
        MsgBox "No column found for " & Format(month, "mmm-yy")
    End If
End Sub

Code: Select all

Sub CopyData()
    Dim month As Date
    Dim colHeader As Range, currMonthCol As Range, prevMonthCol As Range, formulaCol As Range, valuesCol As Range
    Dim lstRw As Long
    ' Get the month selected in S2
    month = Range("S2").Value
    
    ' Look for the column with matching header for the previous month
    Set colHeader = Range("F5:Q5").Find(what:=Format(DateSerial(Year(month), VBA.month(month), 1), "mmm-yy"), _
        LookIn:=xlValues, LookAt:=xlWhole)
    
    If Not colHeader Is Nothing Then
        With ActiveSheet
        
            lstRw = .Cells(.Rows.Count, colHeader.Column - 1).End(xlUp).Row
            
            ' Set the range for the current month column
            Set currMonthCol = .Range(.Cells(colHeader.Row + 1, colHeader.Column), .Cells(lstRw, colHeader.Column))

            ' Set the range for the previous month column
            Set prevMonthCol = currMonthCol.Offset(0, -1)
            
            ' Copy and paste formula from previous month column to current month column
            currMonthCol.Formula = prevMonthCol.Formula
            
            ' Copy and paste values from previous month column to current month column
            prevMonthCol.Value = prevMonthCol.Value
        End With
        Application.CutCopyMode = False ' Clear clipboard
        
    Else
        MsgBox "No column found for " & Format(month, "mmm-yy")
    End If
End Sub