Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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.

    VB:
    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 11:39. Reason: code tags

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,450

    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
    8,450

    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
    VB:
    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
    VB:
    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
    8,450

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

    You can shorten the code like
    VB:
    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 12: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
    VB:
    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, 00:58
  2. Replies: 2
    Last Post: June 23rd, 2008, 01:15
  3. Replies: 8
    Last Post: March 7th, 2008, 06:22
  4. Replies: 4
    Last Post: February 19th, 2008, 17:11
  5. Replies: 4
    Last Post: March 17th, 2007, 15: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