Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 10 of 10

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

  1. #1
    Join Date
    15th December 2011
    Posts
    10

    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 at 12:39. Reason: code tags

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    9,423

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    15th December 2011
    Posts
    10

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    9,423

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    26th April 2010
    Location
    VietNam
    Posts
    49

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th March 2011
    Posts
    52

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    15th December 2011
    Posts
    10

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    15th December 2011
    Posts
    10

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    9,423

    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 at 13:39. Reason: Missed a period.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    15th December 2011
    Posts
    10

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

    Quote 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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 3
    Last Post: April 4th, 2011, 01:58
  2. Replies: 2
    Last Post: June 23rd, 2008, 02:15
  3. Replies: 8
    Last Post: March 7th, 2008, 07:22
  4. Replies: 4
    Last Post: February 19th, 2008, 18:11
  5. Replies: 4
    Last Post: March 17th, 2007, 16:53

Bookmarks

Posting Permissions

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