Excel 2007 - Move data and clear new Fiscal year data rows. Copy and Clear data

[chamdan]

I have worksheet that contains in Column A “Contract No.”, in Column “AU” to “BF” Last Fiscal Year such as every column from AU to BF represent the twelve month of the year e.g. “Apr, May, Jun, July, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar”. Now the first row for each contract a formula is maintained which sum up the product of all the transaction for each contract no. see the example below:



Then in Column “BG” to “BR” the column for the current Fiscal year totals for each month.
So now the objective is that when the date of the system reaches April first in the current year I want all the data to be moved from let us say month of April to last Fiscal year April corresponding row and column. But keep the formula intact in the black row which contains for each month the corresponding formula.
Month April last Fiscal year

=SUMIF($A:$A,"="&INDIRECT("$A"&ROW()+1),AU:AU)

Month April Current Fiscal Year

=SUMIF($A:$A,"="&INDIRECT("$A"&ROW()+1),BG:BG)

As shown above in the picture the first contract # happened to be from Row 6 to row 8 but can be expanded based on the number of transactions. In Month of April Row 6 Column AU will be replaced by the amount found in Row 6 Column BG Current Fiscal Year. Then clear the corresponding row column of the current fiscal year only after copying the value to the corresponding month row & column of the same month but last fiscal year.
This process will be done throughout all the rows until last row containing data.

Hope someone could help me achieve that using VBA so I can integrate that macro and using a button to trigger the macro every time a new fiscal year is reached.

I need your help.

Regards,

Chuck

 

[NoS]

Try this on a copy of your worksheet and let me know if this does what you're wanting.

Option Explicit

Sub MoveAndClearData()
'http://www.excelguru.ca/forums/showthread.php?2304

    Dim rng As Range
    Dim cel As Range
    Dim Lrow As Long
    
Application.ScreenUpdating = False

With Sheets("Sheet1")
    Lrow = .Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = Range("A2:A" & Lrow)
    For Each cel In rng
        If Len(cel.Value) > 0 Then
            With Range("BG" & cel.Row).Resize(1, 12)
                .Copy Range("AU" & cel.Row)
                .ClearContents
            End With
        End If
    Next cel
End With

Application.ScreenUpdating = True

End Sub

Chuck, you should read this.

 

[chamdan]

Thank you for your prompt reply, it worked with only one problem, is that the header of the current fiscal year gets erased starting from BG to BR "April" to "March" otherwise all the results are correct.

Regards,

Chuck

 

[chamdan]

Resolved - Excel 2007 - move data and clear new fiscal year data rows. Copy and Clear

Thank you for your help I realized that the starting row on my worksheet after the heading was 5 and therefore changed the following code:

    Set rng = Range("A2:A" & Lrow)

to

    Set rng = Range("A5:A" & Lrow)

Then my current fiscal year headers remained untouched.

Cheers

Chuckopcorn:

 

[NoS]

Thanks for reporting back. Glad you got it to work. My slip-up when creating a worksheet to test on.

NoS