Assigned cell contents to a variable & using the var in cell reference

[managementOptions]

In this particular task I have many rows of data and want to let the user select a row to bring to the top. Not move the row, just copy it into a blank row near the top of the spreadsheet.

First assume:

The data begins in row 10 and extends down to whatever length.
Row 3 is used for no other reason than to let the user see any line of data he likes without scrolling and searching through all of the rows.
The user uses Cell A1 to specify the row number he wants to see.

So if the user enters 10 into Cell A1, for example, row 10 would be copied to row three.

If the user then enters 15 into Cell A1, row 15 would be copied into row 3. Thus, the many rows of data would remain unchanged, but row 3 would always be reserved for a copy of whatever row number specified by the user.

My assumption is that I would do this with a macro attached to a button for the user. I assume that I would assign the value in cell A1 as the row number to copy and paste into row 10. But I really don't know how to assign the cell contents to the variable (is it a string or number, for example, when used as part of a cell address). I also don't know syntax for using the variable value in assigning the row to copy.

So there are a number of steps with which I need help:

I believe that this would be something like the below. I know that the below is not correct, but I believe it conveys what i want to do. That is, I believe I have the steps correct but the syntax not correct.

'Dim number as [string or integer?]
'myVariable= ActiveCell.("A1")
'ActiveCell..Rows("myVariable:myVariable").EntireRow.Select
'Selection.Copy
'ActiveCell.Rows("10:10").EntireRow.Select
'ActiveSheet.Paste

If anyone can help me correct the above to make it work I would be very grateful.

 

[macropod]

Hi managementOptions,

Try the following in the relevant sheet's Worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet
  If Intersect(Target, .Range("A1")) Is Nothing Then Exit Sub
  If Not IsNumeric(.Range("A1")) Then Exit Sub
  If .Range("A1").Value < 1 Then Exit Sub
  .Range("A" & .Range("A1").Value).EntireRow.Copy
  .Range("A3").PasteSpecial Paste:=xlPasteValues
  .Range("A3").PasteSpecial Paste:=xlPasteFormats
End With
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[strongm]

You may find yourself with more responses if you ask this in the more appropriate forum: forum707

 

[SkipVought]

This member has REPEATEDLY been advised to post in forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}