Selection.End(xldown).Select variable number of times

[Will85]

In column A, I have strategically placed the value of x in specific rows.

I use a macro that goes to column A and then executes selection.end(xldown).Select a certain number of times depending on which row I want it to land on.

For example if I want it to select the third row with an x I use:

Range("A1").Select
selection.end(xldown).Select
selection.end(xldown).Select
selection.end(xldown).Select


I would prefer to be able to tell it to go down a specific # of times based on the value in a cell on another sheet.

Additionally, I need it to look up this value, instead of pointing to a specific cell reference.

For example, Id want to index match the value based on an account number, the account number its looking up can be hardcoded inside the macro itself.

Sheet2 has account numbers in column A and the value I am seeking in column B.

 

[Will85]

Like all things, its more complicated. Id have 12 columns that would need to be adjusted, for each month going accross.

I take it the macro is not inserting a new row first so that the sum follows?

I can use a sumif to accomplish my subtotal, its just not ideal.

Try

Sub Move_with_cells()
Dim Shp As Shape
Set Shp = ActiveSheet.Shapes(Application.Caller)
With Shp.TopLeftCell.EntireRow
   .Offset(-1).Copy
   .Insert xlDown
   .Locked = False
   .FormulaHidden = False
   .Offset(-2).RowHeight = 50
   Range("C" & .Row).Replace ":C*", ":C" & .Row - 1 & ")", xlPart, , False, , False, False
End With
End Sub

 

[Fluff]

I'm just making life more difficult than it needs to be
Try

Sub Move_with_cells()
Dim Shp As Shape
Set Shp = ActiveSheet.Shapes(Application.Caller)
With Shp.TopLeftCell.EntireRow
   .Offset(-1).Copy
   .Offset(-1).Insert xlDown
   .Locked = False
   .FormulaHidden = False
   .Offset(-2).RowHeight = 50
End With
End Sub

 

[Will85]

hahaha, perfect, works like a charm!

Last challenge, I promise. The entire sheet is locked, including the row that I am copying, except that once its copied and inserted above, I need that new row to be unlocked so users can input their data.

I want the original row that is being copied over and over locked, so that someone doesn't accidentally copy down or type data in, and then that data is copied over and over.

All new rows that are being inserted above need to be unlocked, so users can insert their data.

I previously used Selection.Locked=False, but I dont know where to put that in your code.

I'm just making life more difficult than it needs to be
Try

Sub Move_with_cells()
Dim Shp As Shape
Set Shp = ActiveSheet.Shapes(Application.Caller)
With Shp.TopLeftCell.EntireRow
   .Offset(-1).Copy
   .Offset(-1).Insert xlDown
   .Locked = False
   .FormulaHidden = False
   .Offset(-2).RowHeight = 50
End With
End Sub

 

[Fluff]

How about

Sub Move_with_cells()
Dim Shp As Shape
Set Shp = ActiveSheet.Shapes(Application.Caller)
With Shp.TopLeftCell.EntireRow
   .Offset(-1).Copy
   .Offset(-1).Insert xlDown
  [COLOR=#ff0000] .Offset(-2).Locked = False[/COLOR]
   .FormulaHidden = False
   .Offset(-2).RowHeight = 50
End With
End Sub

 

[Will85]

No dice, I get an error on .Offset(-1).Insert xlDown

I think its trying to insert before the sheet is unprotected

I think the sheet needs to be unprotected
The row needs to be copied
The new rows protection needs to be changed from Locked to Unlocked
Then the sheet needs to be protected again.

How about

Sub Move_with_cells()
Dim Shp As Shape
Set Shp = ActiveSheet.Shapes(Application.Caller)
With Shp.TopLeftCell.EntireRow
   .Offset(-1).Copy
   .Offset(-1).Insert xlDown
  [COLOR=#ff0000] .Offset(-2).Locked = False[/COLOR]
   .FormulaHidden = False
   .Offset(-2).RowHeight = 50
End With
End Sub

 

[Fluff]

You never said the sheet was protected
Try

Sub Move_with_cells()
Dim Shp As Shape
ActiveSheet.Protect Password:="abc", userinterfaceonly:=True
Set Shp = ActiveSheet.Shapes(Application.Caller)
With Shp.TopLeftCell.EntireRow
   .Offset(-1).Copy
   .Offset(-1).Insert xlDown
   .Offset(-2).Locked = False
   .FormulaHidden = False
   .Offset(-2).RowHeight = 50
End With
End Sub

 

[Will85]

Thank you!!!! It appears to be working as desired! Thank you for your time and help!

You never said the sheet was protected
Try

Sub Move_with_cells()
Dim Shp As Shape
ActiveSheet.Protect Password:="abc", userinterfaceonly:=True
Set Shp = ActiveSheet.Shapes(Application.Caller)
With Shp.TopLeftCell.EntireRow
   .Offset(-1).Copy
   .Offset(-1).Insert xlDown
   .Offset(-2).Locked = False
   .FormulaHidden = False
   .Offset(-2).RowHeight = 50
End With
End Sub

 

[Will85]

.

Thank you!!!! It appears to be working as desired! Thank you for your time and help!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Will85]

Do you think there might be some settings missing in the code?

When I protect the sheet, save and close it, then re-open. If I insert new rows, any cell that I click on in the new row is not being highlighted on screen. I can type and manipulate, but the normal square with the fill arrow does not appear on screen on any cell. On other tabs in the same workbook I get the normal square around a selected cell.

Additonally, if I save and protect with delete rows enabled. Once I close the workbook and re-open, I am unable to delete any of the unprotected rows the macro has added.

You're welcome & thanks for the feedback