Macro VBA code runs fine when running independently but becomes broken when run in a string with other macros

Question

I have a string of steps I turned into one macro. When run independently they are all fine but something happens on step 18 when it is run collectively that is causing a problem where the "place I am pasting to is not the same size as my copy". Can any of you help? My code should be okay as it behaves properly when I select step 18 specifically but I'm sure I'm doing something horribly wrong. This is my first time using VBA so anything helps! The goal of this is to filldown a vlookup statement to a dynamic range in columns P,Q, and R (Column R takes its values from another sheet within the workbook) The place it says it gets stuck on is:

"Range("P2:P" & LR).PasteSpecial xlPasteAll"

For reference here is the step before 18 and step 18's code:

Step 17:

Sub Step17_2()
'
' Step17_2 Macro
'

'
    Columns("C:C").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("D:M").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlToLeft
End Sub

Step 18:

Sub Step18()
'
' Step18 Macro
'

'
    Sheets("Contact").Select
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "VlookupType"
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "VlookupIP"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "VlookupMailingName"
    Range("P1").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Dim LR As Integer
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("P2").Formula = "=VLOOKUP(A2,PastedValues!A$2:D$" & LR & ",2,FALSE)"
Range("P2").Copy
Range("P2:P" & LR).PasteSpecial xlPasteAll
Application.CutCopyMode = False
Range("Q2").Formula = "=VLOOKUP(A2,PastedValues!A$2:D$" & LR & ",4,FALSE)"
Range("Q2").Copy
Range("Q2:Q" & LR).PasteSpecial xlPasteAll
Application.CutCopyMode = False
Sheets("ContactDetailed").Select
Dim TA As Integer
TA = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Contact").Select
Range("R2").Formula = "=VLOOKUP(A2,ContactDetailed!A$2:D$" & TA & ",4,FALSE)"
Range("R2").Copy
Range("R2:R" & LR).PasteSpecial xlPasteAll
Application.CutCopyMode = False
End Sub

To begin with, you want to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. It is likely if you do, the problem will be fixed as the active cell is not always the one you think it is — cybernetic.nomad, Mar 25 '19 at 17:58
And just `Range("P2:P" & LR).Formula = "=VLOOKUP(A2,PastedValues!A$2:D$" & LR & ",2,FALSE)"` would do ;). And...dont use `Integers` is something I could recommend! — JvdV, Mar 25 '19 at 18:00
@JvdV thanks for your answer, I tried switching that out but it hits an error there as well. so now it is `Dim LR As Integer LR = Range("A" & Rows.Count).End(xlUp).Row Range("P2:P" & LR).Formula = "=VLOOKUP(A2,PastedValues!A$2:D$" & LR & ",2,FALSE" Application.CutCopyMode = False` error is from Range to end of VLookUp formula — Mackenzie Jones, Mar 25 '19 at 18:07

JvdV · Accepted Answer · 2019-03-25T18:35:29.593

Its some raw code you have there, some tips:

Try and avoid the use of .Select, read here why and how.
Don't use Integer variables. Why? Your data might at some point have more data an integer variable could store, namely -32,768 to 32,767. The use of Long or Double won't get you in future troubles.
It's a good practice to store your variables at the start of your code, preferably using Option Explicit. That way you won't miss any undeclared variable.
A lot of lines could actually be combined or are becomming useless when you would code straight in VBA instead of recording a macro.

Below is an example of how I would interpret your step 18:

Dim LR As Double, TA As Double

Option Explicit

Sub Step18()

TA = ActiveWorkbook.Sheets("ContactDetailed").Range("A" & Rows.Count).End(xlUp).Row
With ActiveWorkbook.Sheets("Contact")
    .Range("P1").FormulaR1C1 = "VlookupType"
    .Range("Q1").FormulaR1C1 = "VlookupIP"
    .Range("R1").FormulaR1C1 = "VlookupMailingName"
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("P2:P" & LR).Formula = "=VLOOKUP(A2,PastedValues!A$2:D$" & LR & ",2,FALSE)"
    .Range("Q2:Q" & LR).Formula = "=VLOOKUP(A2,PastedValues!A$2:D$" & LR & ",4,FALSE)"
    .Range("R2:R" & LR).Formula = "=VLOOKUP(A2,ContactDetailed!A$2:D$" & TA & ",4,FALSE)"
End With

End Sub

Also had a look at your step 17_2 and you could simplify that like this for example:

Sub Step17_2()

With ActiveWorkbook.Sheets("Contact") 'I assume you meant this sheet, change if need be
    .Range("C:C,E:N,P:AA").EntireColumn.Delete 'I used AA but you could also change this
End With

End Sub

Thank you @JvdV that worked much faster! The only issue I am having is that the Vlookup is not grabbing from the correct table. Column P should grab from column two in the sheet named PastedValues, Column Q should grab from column 4 in the same sheet and Column R should grab from Column 4 in the sheet named ContactDetailed. I think it may be because of this piece: `Dim LR As Double, TA As Double Option Explicit` It adds it to the end of step 17_2 rather than letting me claim those as variables. — Mackenzie Jones, Mar 25 '19 at 18:49
Option explicit goes on top of your module, not a single macro. Variables declared above it. Look up an example on the internet. — JvdV, Mar 25 '19 at 19:52

Macro VBA code runs fine when running independently but becomes broken when run in a string with other macros

1 Answers1