Help simplifying VBA code

[Final Frontier]

I just started reading up on VBA and many people suggest recording to get an idea of what code looks like. I recorded a similar process that I need to do several times throughout my workbook 4 times and got several pages of code. From various posts it seems like recording gives you way more code than you need. Can anyone give me some hints on how to trim this down? I also need pointers on how to repeat this process several times within a spreadsheet. I'm basically compiling/calculating data between two tabs and entering it on a third tab. It's basically part copying and pasting and part multiplication. Are there any functions that would help simplify? Or maybe I shouldn't even be using VBA for this type of work? I'm struggling on where to begin. Thanks.

Sub AllocationTest1()
Sheets("BI UPLOAD FILE").Select
Range("B2").Select
Sheets("BI INFO").Select
Range("I4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("BI UPLOAD FILE").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "='ALLOCATION INFO'!R2C[1]*'BI INFO'!R[2]C[10]"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C61"), Type:=xlFillDefault
Range("C2:C61").Select
Range("D2").Select
ActiveCell.FormulaR1C1 = "='ALLOCATION INFO'!R2C*'BI INFO'!R[2]C[10]"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D61"), Type:=xlFillDefault
Range("D2:D61").Select
Range("E2").Select
Sheets("BI INFO").Select
Range("K4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("BI UPLOAD FILE").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F2").Select
Sheets("BI INFO").Select
Range("C4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("BI UPLOAD FILE").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G2").Select
Sheets("ALLOCATION INFO").Select
Range("G2:H2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("BI UPLOAD FILE").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("G2:H61"), Type:=xlFillDefault
Range("G2:H61").Select
Selection.FillDown
Columns("A:H").Select
Range("A22").Activate
Columns("A:H").EntireColumn.AutoFit
Range("B62").Select
Sheets("BI INFO").Select
Range("I4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("BI UPLOAD FILE").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C62").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "='ALLOCATION INFO'!R2C[1]*'BI INFO'!R[-58]C[10]"
Range("C62").Select
Selection.AutoFill Destination:=Range("C62:C121"), Type:=xlFillDefault
Range("C62:C121").Select
Range("D62").Select
ActiveCell.FormulaR1C1 = "='ALLOCATION INFO'!R3C*'BI INFO'!R[-58]C[10]"
Range("D62").Select
Selection.AutoFill Destination:=Range("D62:D121"), Type:=xlFillDefault
Range("D62:D121").Select
Range("C62").Select
ActiveCell.FormulaR1C1 = "='ALLOCATION INFO'!R3C[1]*'BI INFO'!R[-58]C[10]"
Range("C62").Select
Selection.AutoFill Destination:=Range("C62:C121"), Type:=xlFillDefault
Range("C62:C121").Select
Range("E62").Select
Range("E2:F61").Select
Selection.Copy
Range("E62").Select
ActiveSheet.Paste
Range("G62").Select
Sheets("ALLOCATION INFO").Select
Range("G3:H3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("BI UPLOAD FILE").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("G62:H121"), Type:=xlFillDefault
Range("G62:H121").Select
Selection.FillDown
Range("B122").Select
Sheets("BI INFO").Select
Selection.Copy
Sheets("BI UPLOAD FILE").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C122").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "='ALLOCATION INFO'!R4C[1]*'BI INFO'!R[-118]C[10]"
Range("C122").Select
Selection.AutoFill Destination:=Range("C122:C181"), Type:=xlFillDefault
Range("C122:C181").Select
Range("D122").Select
ActiveCell.FormulaR1C1 = "='ALLOCATION INFO'!R4C*'BI INFO'!R[-118]C[10]"
Range("D122").Select
Selection.AutoFill Destination:=Range("D122:D181"), Type:=xlFillDefault
Range("D122:D181").Select
Sheets("BI INFO").Select
Range("K4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("BI UPLOAD FILE").Select
Range("E122").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F122").Select
Sheets("ALLOCATION INFO").Select
Range("A41").Select
Sheets("BI INFO").Select
Range("C4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("BI UPLOAD FILE").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G122").Select
Sheets("ALLOCATION INFO").Select
Range("G4:H4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("BI UPLOAD FILE").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G122:H122").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("G122:H181"), Type:=xlFillDefault
Range("G122:H181").Select
Range("B182").Select
Sheets("BI INFO").Select
Range("I4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("BI UPLOAD FILE").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C182").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "='ALLOCATION INFO'!R5C[1]*'BI INFO'!R[-178]C[10]"
Range("C182").Select
Selection.AutoFill Destination:=Range("C182:C241"), Type:=xlFillDefault
Range("C182:C241").Select
Range("D182").Select
ActiveCell.FormulaR1C1 = "='ALLOCATION INFO'!R5C*'BI INFO'!R[-178]C[10]"
Range("D182").Select
Selection.AutoFill Destination:=Range("D182:D241"), Type:=xlFillDefault
Range("D182:D241").Select
Sheets("BI INFO").Select
Range("K4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("BI UPLOAD FILE").Select
Range("E182").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F182").Select
Sheets("BI INFO").Select
Range("C4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("BI UPLOAD FILE").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G182").Select
Sheets("ALLOCATION INFO").Select
Range("G5:H5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("BI UPLOAD FILE").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G182:H182").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("G182:H241"), Type:=xlFillDefault
Range("G182:H241").Select
Selection.FillDown
Range("C2:D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "0.00"
Columns("A:H").Select
Columns("A:H").EntireColumn.AutoFit
Range("A1").Select
ActiveWindow.SmallScroll Down:=-9
End Sub

 

[par60056]

Welcome to the forum. You will find lots of help.

First, when posting code, please use the [ c o d e ] and [ / c o d e ] tags (no spaces) around the code. It makes the post easier to read.

second, The macro recorder like to select everything and then work on selection. It is almost never needed to select things in the macro and it slows everything down.

I replaced the first 10 lines of your code with the following.

Sheets("BI INFO").Range("I4").End(xlDown).Copy
Sheets("BI UPLOAD FILE").Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("BI UPLOAD FILE").Cells(2, 3).FormulaR1C1 = "='ALLOCATION INFO'!R2C[1]*'BI INFO'!R[2]C[10]"
Sheets("BI UPLOAD FILE").Range("C2").AutoFill Destination:=Range("C2:C61"), Type:=xlFillDefault

The recorder also records movement like "ActiveWindow.SmallScroll Down:=-9" any of these can be removed.

 

[par60056]

That code was a little extreme in trying not to change what is active.

I generally will make the coding easier by activating the sheet.

Sheets("BI INFO").Range("I4").End(xlDown).Copy
Sheets("BI UPLOAD FILE").Activate
Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Cells(2, 3).FormulaR1C1 = "='ALLOCATION INFO'!R2C[1]*'BI INFO'!R[2]C[10]"
Range("C2").AutoFill Destination:=Range("C2:C61"), Type:=xlFillDefault

 

[par60056]

another simplification. I don't normally use R1C1 references but I think you can replace:

Cells(2, 3).FormulaR1C1 = "='ALLOCATION INFO'!R2C[1]*'BI INFO'!R[2]C[10]"
Range("C2").AutoFill Destination:=Range("C2:C61"), Type:=xlFillDefault

with

Range("C2:C61").FormulaR1C1 = "='ALLOCATION INFO'!R2C[1]*'BI INFO'!R[2]C[10]"

 

[Final Frontier]

Thanks for your help! I tried trimming down and I am starting to understand how this works a little better, but I do have some questions. I pasted the new code below, it's definitely not perfect but a little better. I'm confused because the code is only copying cell I4 from my first tab and not copying down. Isn't the first line (Sheets("BI INFO").Range("I4").End(xlDown).Copy) supposed to highlight the data and copy? It's only bringing back I4 when I run. Do you know what I'm missing?

Could you also give an explanation for how the code knows where to grab the information from? For example the formulas show up as: Range("C2").FormulaR1C1 = "='ALLOCATION INFO'!R2C[1]*'BI INFO'!R[2]C[10]. The actual formula is ='ALLOCATION INFO'!D$2*'BI INFO'!M4. I'm struggling to find the relationship between the two.

    Sheets("BI INFO").Range("I4").End(xlDown).Copy
    Sheets("BI UPLOAD FILE").Activate
    Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C2").FormulaR1C1 = "='ALLOCATION INFO'!R2C[1]*'BI INFO'!R[2]C[10]"
    Range("C2").AutoFill Destination:=Range("C2:C61"), Type:=xlFillDefault
    Range("D2").FormulaR1C1 = "='ALLOCATION INFO'!R2C*'BI INFO'!R[2]C[10]"
    Range("D2").AutoFill Destination:=Range("D2:D61"), Type:=xlFillDefault
    Sheets("BI INFO").Range("K4").End(xlDown).Copy
    Sheets("BI UPLOAD FILE").Activate
    Range("E2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("BI INFO").Range("C4").End(xlDown).Copy
    Sheets("BI UPLOAD FILE").Activate
    Range("F2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("ALLOCATION INFO").Range("G2:H2").Copy
    Sheets("BI UPLOAD FILE").Activate
    Range("G2:H2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("G2:H61").AutoFill Destination:=Range("G2:H61"), Type:=xlFillDefault
    Range("A1").Select
End Sub

 

[par60056]

As I said, I generally don't us R1C1 for formulas. If I am reading your message correctly, I would change:

Range("C2").FormulaR1C1 = "='ALLOCATION INFO'!R2C[1]*'BI INFO'!R[2]C[10]"

to something like :

Range("C2:C61").Formula = "='ALLOCATION INFO'!D$2*'BI INFO'!M4"

(though I am a little concerned about this going in row and referencing M4. Should this be $M$4 or is it really offset 2 rows on that sheet?)

R1C1 formulas can be helpful in some situations but I generally find it not intuitive and prefer the other addressing.

.End(xlDown) has a problem in that it stops at the first blank. So if I5 is blank it will stop.

 

[Final Frontier]

Ok, I looked into this further and now see what you are saying. Thanks.

The reason for the absolute is because I'm taking a percentage for one customer and multiplying it by several different items and prices. So ultimately my code will need to repeat several times with minor adjustments to the formula each time.

This brings me to my next (and hopefully last) question. I will need this code to repeat several times but if I specify a range as I'm doing above (Range("C2:C61")) my code will only work if each month my data set is the same amount of lines. I need to only grab the information under the specific column regardless of how many rows contain information. For example one month the cell might end at C40 but another month it could end at C80. I'm looking for a way to account for that kind of change. Also, when the code is set to paste the data I want to make sure with each loop the data pastes immediately after eachother and does not overwrite anything. Not sure if there is a certain way to do that. So can I set a specific range and have the vba keep looping through with the same criteria but slightly different formulas?

 

[par60056]

Always Dim variables that will be used for row numbers as long. Yes it is not common to have more than 32000 rows BUT get in the habit early and it will never bite you. Working with a long instead of an integer is meaningless these days.

dim lastRow as Long

lastRow = range("C1000000").end(xlUp).Row
[COLOR=#333333]Range("C2:C" & lastRow).Formula = ….
[/COLOR]