Announcement

Collapse
No announcement yet.

VBA copy range from one sheet to another, then move range a few columns and redo

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA copy range from one sheet to another, then move range a few columns and redo

    I have written a code that enables me to do this, however it is quite long and inneficient as to it will be difficult to make changes afterwards....I therefore need it to be shorter, efficient and flexible.

    I currently have two sheets, CC27 and CC 27 Expenditure Forecast. I want to take data from CC 27 Expenditure Forecast and dump it into sheet CC27. I would then do this over and over for other cost centers. Sheet CC27 has 10 columns as titled below. My code basically enters the cc# manually (its written in the code), finds the apppropriate data from CC 27 Expenditure Forecast. I am essentially always using row 2 to get my Fund, row 3 to get my Project, and column A to get my line objects. The tricky part is that the rest of my details are in range H7:M120 to start, and then other projects are entered in the similar range besides that or N7:R120 and so on and so on, so my range moves by 6 rows everytime.

    I would like my code to be efficient so that I can change my starting range if I need to and change how many rows the range needs to move over...

    Here is the code, and below a table describing what I am trying to do.

    Code:
    Sub Consolidate27()
    '
    ' consolidate Macro
    ' Macro recorded 1/27/2012 by Jean-Francois Gauthier
    '
    ' Keyboard Shortcut: Ctrl+Shift+C
    '
    '
    ' tests Macro
    ' Macro recorded 2/08/2012 by Jean-Francois Gauthier
    '
    ' Keyboard Shortcut: Ctrl+Shift+N
    '
    Sheets("CC-27").Select
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "CC"
    With ActiveCell.Characters(Start:=1, Length:=2).Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 10
    End With
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "FUND"
    With ActiveCell.Characters(Start:=1, Length:=4).Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 10
    End With
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "PROJ"
    With ActiveCell.Characters(Start:=1, Length:=4).Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 10
    End With
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "LINE OBJECT"
    With ActiveCell.Characters(Start:=1, Length:=11).Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 10
    End With
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "BUDGET"
    With ActiveCell.Characters(Start:=1, Length:=6).Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 10
    End With
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "EXPENDITURES TO DATE"
    With ActiveCell.Characters(Start:=1, Length:=20).Font
    End With
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "FORECAST TO YEAR END"
    With ActiveCell.Characters(Start:=1, Length:=20).Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 10
    End With
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "FORECAST AT YEAR END"
    With ActiveCell.Characters(Start:=1, Length:=20).Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 10
    End With
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "VARIANCE"
    With ActiveCell.Characters(Start:=1, Length:=8).Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 10
    End With
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "COMMENTS"
    With ActiveCell.Characters(Start:=1, Length:=8).Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 10
    End With
    
    Columns("E:I").Select
    Range("I1").Activate
    Selection.Style = "Currency"
    Columns("A:D").Select
    Range("D1").Activate
    Selection.NumberFormat = "@"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "'27"
    Range("B2").Select
    Sheets("CC 27 Expenditure Forecast").Select
    Range("I1").Select
    Selection.Copy
    Sheets("CC-27").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("CC 27 Expenditure Forecast").Select
    Range("I2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("CC-27").Select
    Range("C2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("CC 27 Expenditure Forecast").Select
    Range("A7:A120").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("CC-27").Select
    Range("D2").Select
    ActiveSheet.Paste Link:=True
    Range("E2").Select
    Sheets("CC 27 Expenditure Forecast").Select
    Range("H7:M120").Select
    Range("H120").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("CC-27").Select
    ActiveSheet.Paste Link:=True
    Range("A2:C2").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("A2:C115")
    
    Range("A116").Select
    ActiveCell.FormulaR1C1 = "'27"
    Range("B116").Select
    Sheets("CC 27 Expenditure Forecast").Select
    ActiveWindow.SmallScroll ToRight:=7
    Range("O1").Select
    Selection.Copy
    Sheets("CC-27").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("CC 27 Expenditure Forecast").Select
    Range("O2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("CC-27").Select
    Range("C116").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("CC 27 Expenditure Forecast").Select
    Range("A7:A120").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("CC-27").Select
    Range("D116").Select
    ActiveSheet.Paste Link:=True
    Sheets("CC 27 Expenditure Forecast").Select
    Range("N7:S120").Select
    Range("N120").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("CC-27").Select
    Range("E116").Select
    ActiveSheet.Paste Link:=True
    Range("A116:C116").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("A116:C229")
    
    Range("A230").Select
    ActiveCell.FormulaR1C1 = "'27"
    Range("B230").Select
    Sheets("CC 27 Expenditure Forecast").Select
    ActiveWindow.SmallScroll ToRight:=7
    Range("U1").Select
    Selection.Copy
    Sheets("CC-27").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("CC 27 Expenditure Forecast").Select
    Range("U2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("CC-27").Select
    Range("C230").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("CC 27 Expenditure Forecast").Select
    Range("A7:A120").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("CC-27").Select
    Range("D230").Select
    ActiveSheet.Paste Link:=True
    Sheets("CC 27 Expenditure Forecast").Select
    Range("T7:Y120").Select
    Range("T120").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("CC-27").Select
    Range("E230").Select
    ActiveSheet.Paste Link:=True
    Range("A230:C230").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("A230:C343")
    This keeps going until range DR7:DW120....theoretically it go could further, it could end before....something I'd like to put in the code for a quick edit.


    Row CC FUND PROJ LINE OBJECT BUDGET EXPENSES FORECAST TOTAL FORECAST VARIANCE COMMENTS
    2 "27" I2 I3 A7 H7 I7 J7 K7 L7 M7
    3 "27" I2 I3 A8 H8 I8 J8 K8 L8 M8
    4 "27" I2 I3 A9 H9 I9 J9 K9 L9 M9
    ... .... ..... ..... .....
    116 "27" I2 I3 A120 H120 I120 J120 K120 L120 M120
    117 "27" N2 N3 A7 M7 N7 O7 P7 Q7 R7
    118 "27" N2 N3 A8 M8 N8 O8 P8 Q8 R8
    ......
    230 "27" N2 N3 A120 M120 N120 0120 P120 Q120 R120
    Last edited by jean004; February 17th, 2012, 12:39. Reason: code tags

  • #2
    Re: VBA copy range from one sheet to another, then move range a few columns and redo

    There would be no one, I maybe wrong, to read all through your recorded macro code.

    I think it's better to upload a sample file(data could be fake, but sheet layouts must be identical to the real file) and the results that you want with the clear explanation.

    Comment


    • #3
      Re: VBA copy range from one sheet to another, then move range a few columns and redo

      Ok, I will try and upload something in the next few hours.

      Comment


      • #4
        Re: VBA copy range from one sheet to another, then move range a few columns and redo

        jean004

        Please go back to your post#1 and Edit to enclose your code with the code tags.

        [code]
        'your code
        [/code]

        Otherwise, someone will come to arrest you.

        Comment


        • #5
          Re: VBA copy range from one sheet to another, then move range a few columns and redo

          To Jean004, I will adjust a part yourcode, After
          Code:
          Range("A1").Select
              ActiveCell.FormulaR1C1 = "CC"
              With ActiveCell.Characters(Start:=1, Length:=2).Font
                  .Name = "Arial"
                  .FontStyle = "Bold"
                  .Size = 10
                  .Strikethrough = False
                  .Superscript = False
                  .Subscript = False
                  .OutlineFont = False
                  .Shadow = False
                  .Underline = xlUnderlineStyleNone
                  .ColorIndex = xlAutomatic
              End With
          To
          Code:
          With Range("A1")
              .FormulaR1C1 = "CC"
              With .Characters(Start:=1, Length:=2).Font
                  .Name = "Arial"
                  .FontStyle = "Bold"
                  .Size = 10
              End With
          End With
          You should follow me to adjust Yourcode to short code and nice looking

          Comment


          • #6
            Re: VBA copy range from one sheet to another, then move range a few columns and redo

            Wow... how long did it take you to record that macro haha!

            Can I just make a suggestions and please don't take this as an insult or anything... The process you want to automate is very basic, moving around data with a few conditions and loops. It might be a better idea to learn the very basics of VBA code and write the macro yourself. You would only need a basic understanding of Range objects, Sheet objects, For loops and If statements to do this task.

            Comment


            • #7
              Re: VBA copy range from one sheet to another, then move range a few columns and redo

              Thank you, I made this change which does shorten the code a bit. Thank you.

              Comment


              • #8
                Re: VBA copy range from one sheet to another, then move range a few columns and redo

                I do have limited knowledge of VBA and IF and Loop statement, and that is exactly where I am trying to go. I am just not sure how to go abouts having my range move over by 6 columns every time.

                I'm not looking for someone to necessarily fix my code completely but help me along...I do want to learn something through this rather than simply be given the answer.

                Comment


                • #9
                  Re: VBA copy range from one sheet to another, then move range a few columns and redo

                  You can shorten the code like
                  Code:
                      With Sheets("CC-27")
                          With .Range("a1:j1")
                              .Value = Split("CC,FUND,PROJ,LINE OBJECT,BUDGET,EXPENDITURES TO DATE" & _
                               ",FORCAST TO YEAR END,FORCAST AT YEAR END,VARIANCE,COMMENTS", ",")
                               With .Font
                                  .Name = "Arial"
                                  .Size = 10
                                  .Bold = True
                               End With
                              End With
                          .Columns("E:I").Style = "Currency"
                          .Columns("A:D").NumberFormat = "@"
                          .Range("A2").Value = "'27"
                      End With
                  It is upto before copy paste action.
                  Last edited by jindon; February 17th, 2012, 13:39. Reason: Missed a period.

                  Comment


                  • #10
                    Re: VBA copy range from one sheet to another, then move range a few columns and redo

                    Originally posted by jindon View Post
                    You can shorten the code like
                    Code:
                        With Sheets("CC-27")
                            With .Range("a1:j1")
                                .Value = Split("CC,FUND,PROJ,LINE OBJECT,BUDGET,EXPENDITURES TO DATE" & _
                                 ",FORCAST TO YEAR END,FORCAST AT YEAR END,VARIANCE,COMMENTS", ",")
                                 With .Font
                                    .Name = "Arial"
                                    .Size = 10
                                    .Bold = True
                                 End With
                                End With
                            .Columns("E:I").Style = "Currency"
                            .Columns("A:D").NumberFormat = "@"
                            .Range("A2").Value = "'27"
                        End With
                    It is upto before copy paste action.

                    Thanks Jindon...also a great trick and help. Thank you.

                    Comment

                    Working...
                    X