VBA inserting in wrong sheet

[PierreAB]

Hey guys!
I have been working on a project for a while to automate a task for my work. Final steps now, but for some reason my code is not working as it should, even if I try different versions.

Sub InsertColumnsAndLabel()
' Select the "YT" sheet
Sheets("YT").Select

' Insert 4 columns after column B
Range("C1:F1").EntireColumn.Insert

' Label the columns
Range("C1").Value = "imps"
Range("D1").Value = "Spend"
Range("E1").Value = "Views"
Range("F1").Value = "Conv."
End Sub


This is one of the ones I have tried.
My issue is that even if I select or activate the sheet labeled as "YT", the next lines all end up being applied to another sheet in the workbook called "AutoBUILD".

Thanks for your help!
Pierre

 

[I_know_nuffin]

have you tried

Sheets("YT").Range("C1:F1").EntireColumn.Insert

 

[PierreAB]

I have
It inserted the columns in the right sheet and then went labeled the columns in the other sheet

 

[PierreAB]

have you tried

Sheets("YT").Range("C1:F1").EntireColumn.Insert

PierreAB​

New Member​

JoinedSep 6, 2023 Messages2 Office Version

1. 365

Platform

1. MacOS

• A moment ago
• New
• 
  
• Add bookmark
• #3

I have
It inserted the columns in the right sheet and then went labeled the columns in the other sheet

 

[PierreAB]

have you tried

Sheets("YT").Range("C1:F1").EntireColumn.Insert

Got it to work with this:

Sub InsertColumnsAndLabel3()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("YT")

' Check if the sheet exists
If Not ws Is Nothing Then
' Insert 4 columns after column B
ws.Columns("C:F").Insert Shift:=xlToRight

' Label the columns
ws.Cells(1, 3).Value = "imps"
ws.Cells(1, 4).Value = "Spend"
ws.Cells(1, 5).Value = "Views"
ws.Cells(1, 6).Value = "Conv."

' Find the last used row in column B
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

' Apply the VLOOKUP formula to the "imps" column
ws.Range("C2:C" & lastRow).Formula = "=VLOOKUP(B2, PR!A:C, 2, FALSE)"

' Apply the VLOOKUP formula to the "Spend" column
ws.Range("D2:D" & lastRow).Formula = "=VLOOKUP(B2, PR!A:C, 3, FALSE)"

' Apply the VLOOKUP formula to the "Views" column
ws.Range("E2:E" & lastRow).Formula = "=VLOOKUP(B2, PR!A:J, 4, FALSE)"

' Apply the VLOOKUP formula to the "Conv." column
ws.Range("F2:F" & lastRow).Formula = "=VLOOKUP(B2, PR!A:J, 5, FALSE)"
Else
MsgBox "The sheet 'YT' does not exist."
End If
End Sub