Results 1 to 5 of 5

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:
    Click image for larger version. 

Name:	Excel.png 
Views:	10 
Size:	17.0 KB 
ID:	1796


    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
    HTML Code:
    =SUMIF($A:$A,"="&INDIRECT("$A"&ROW()+1),AU:AU)
    Month April Current Fiscal Year
    HTML Code:
    =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

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Try this on a copy of your worksheet and let me know if this does what you're wanting.

    Code:
    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.

  3. #3
    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

  4. #4

    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:
    HTML Code:
        Set rng = Range("A2:A" & Lrow)
    to
    HTML Code:
        Set rng = Range("A5:A" & Lrow)
    Then my current fiscal year headers remained untouched.

    Cheers

    Chuck

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Thanks for reporting back. Glad you got it to work. My slip-up when creating a worksheet to test on.

    NoS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •