1

The code below will insert a new row above column 2 whenever the macro is ran. However, if I click a different sheet and run the macro, it inserts a new row in that sheet instead of Sheet 1 like I had specified. Is there a way to make it so that when I run the macro it will only do those actions on a particular sheet no matter what sheet I am on?

With Worksheets("Sheet 1")
    Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End With
L42
  • 19,427
  • 11
  • 44
  • 68
THAT newbie
  • 347
  • 1
  • 9
  • 20

3 Answers3

4

Put a dot before "Rows"

Your reference "Rows" is using whatever active Rows (equivalent to ActiveSheet.Rows), but the dot will use the "With" block.

With Worksheets("Sheet 1")
    .Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End With

Without the dot, the With block isn't even considered and the code is equivalent to:

 Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

This is also true for the bang operator (!).

Reference:

Don Jewett
  • 1,867
  • 14
  • 27
  • What a concept. I appreciate it. – THAT newbie Jul 15 '15 at 22:33
  • @THATnewbie For additional information, do not forget to visit [MSDN](https://msdn.microsoft.com/en-us/library/wc500chb.aspx). To know more about coding explicitly in VBA, [this is a good read](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). – L42 Jul 15 '15 at 22:36
  • @L42 That link is actually not the right one-- it's for Visual Basic .NET – Don Jewett Jul 15 '15 at 22:40
2

For a single line of code I would not bother with a With statement:

Worksheets("Sheet 1").Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Dan Donoghue
  • 6,056
  • 2
  • 18
  • 36
0

Add '.' in Rows ...

With Worksheets("Sheet 1")
     .Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End With
san-san
  • 64
  • 8