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

[jean004]

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.

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

[jindon]

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.

[jean004]

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

[jindon]

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.

[nmhung49]

To Jean004, I will adjust a part yourcode, After

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

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

[Silenxor]

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.

[jean004]

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

[jean004]

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.

[jindon]

You can shorten the code like

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.

[jean004]

You can shorten the code like

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.