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!

 

[JLGWhiz]

I have not tried it, but it sounds like the recorder does not recognized that you want the column added inside the table and it is treating the table as a range object and inserting the column B range outside the table range. The normal syntax for adding ranges inside the table range would refer to the ListObject and ListColumns, etc.

 

[Fluff]

Another possibility is that you have merged cells in that column.

 

[DakotaV]

I changed the code up to this and this works fine:

Sub InsertFirstName()

ActiveSheet.Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveSheet.Range("B4").Value2 = "First Name"
ActiveSheet.Range("B5").FormulaR1C1 = "=VLOOKUP(RC[-1],Append1,3,FALSE)"
ActiveSheet.Range("B5").AutoFill Destination:=Range("B5:B49"), Type:=xlFillDefault

End Sub

Now I stilll need to run the macro on every sheet I need it on. Is there a way to add the sheet names to the macro? The sheet names should always be the same.

 

[Fluff]

How many sheets should it work on & how many should it ignore?

 

[DakotaV]

There is one to ignore and nine to update. All sheets have different names but remain static, so they stay the same in other workbooks.

 

[Fluff]

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

 

[DakotaV]

It works but it adds the column also to the tenth sheet, this one should not be changed.

 

[Fluff]

Oops should have mentioned to change the name "Data" to match the sheet you want to ignore.

 

[DakotaV]

Ah yes I should've noticed, I'm a noob at this.