Macro inserts column in wrong place

[DakotaV]

Hello,

I usually code macro's in Excel but I need the process automated. I tried to record a macro but for some reason it goes horribly wrong. This is what I want it to do (and what I recorded):

- Insert a column in a table
- Do a VLOOKUP
- Do this for all remaining rows

When I recorded the macro it looked like this:

Sub InsertFirstName()

Columns("B:B").Select

Range("B3").Activate

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range("B4").Select

ActiveCell.FormulaR1C1 = "First Name"

Range("B5").Select

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Append1,3,FALSE)"

Range("B5").Select

Selection.AutoFill Destination:=Range("B5:B49"), Type:=xlFillDefault

Range("B5:B49").Select

ActiveWindow.SmallScroll Down:=-12

End Sub

When I run the macro above for some reason it inserts a column in front of my table and moves my table a couple of cells to the right. The place where I want the new column with the Vlookup is column B. So the original column B would go to the right side and change to C.

I would really appreciate some help on how I can fix this code or how I can properly record a macro?

Thanks in advance!

 

[DakotaV]

Ok, how about

Sub InsertFirstName()
   Dim Ws As Worksheet
   For Each Ws In Worksheets
      If Ws.Name <> "Data" Then
         Ws.Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
         Ws.Range("B4").Value2 = "First Name"
         Ws.Range("B5:B49").FormulaR1C1 = "=VLOOKUP(RC[-1],Append1,3,FALSE)"
      End If
   Next Ws
End Sub

This code worked like a charm. Thank you very much for this, I appreciate the help!

 

[Fluff]

You're welcome & thanks for the feedback