Macro data range doesn't change?

[MisterMo]

Thanks guys for the advice, instruction you give here, it is APPRECIATED. I (a macro novice), am confused by why a macro won't reflect changes within ranges automatically.

I have a simple macro that copies one row (15), then "insert pastes" that copied row, into the original copied rows location, moving the original row down one row (becoming row 16). The original last row was 115 and now is 116.

The problem I'm having is I have other macros that sort that data in a range that should always begin at row 15, to the last row (originally row 115). After I run the "copying macro", the range of data to be sorted (in the "sorting macros"), has not increased.

I know formulas increase/decrease themselves when row/columns are added/deleted, but why don't macros do the same.

Suggestions, please. Thanks again.

 

[Simon Lloyd]

Well firstly where's the code? we cant help with your code if you don't supply it but for simple copy and paste use this

Sheets("Sheet1").Rows("15:15").Copy Destination:= Sheets("Sheet2").Range("A" & rows .count).end(xlup).offset(1,0)

this copies Sheet1 row 15 to sheet2 next available row.

 

[AlphaFrog]

One thing macros will never do is read your mind.

If you made the macro to sort on rows 15 to 115, it then will do exactly that. If you make your macro determine the last used row on your sheet so that it will increase it's range of cells it sorts on, it then will do exactly that. It all depends on how you constructed the macro.

Post your recorded macro here and you'll get a lot of people willing to show you how to change it so it will determine the last used row.

 

[MisterMo]

Thanks, here ya go:

Sub Sort_X_Name()
'
' Sort_X_Name Macro
' Sorts "Members" worksheet by NAME
'
' Keyboard Shortcut: Ctrl+n
'
Sheets("Members").Select
Rows("15:115").Select
ActiveWindow.LargeScroll Down:=-2
Selection.Sort Key1:=Range("A15"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("H20").Select
End Sub

 

[Simon Lloyd]

try thsi

Sub Sort_X_Name()
'
' Sort_X_Name Macro
' Sorts "Members" worksheet by NAME
'
' Keyboard Shortcut: Ctrl+n
Sheets("Members").Rows("15:" & Sheets("Members").UsedRange.Rows.Count).Sort Key1:=Range("A15"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("H20").Select
End Sub

 

[AlphaFrog]

Yet another way...

Sub Sort_X_Name()
    '
    ' Sort_X_Name Macro
    ' Sorts "Members" worksheet by NAME
    '
    ' Keyboard Shortcut: Ctrl+n
    '
    
    Dim Lastrow As Long
    
    Sheets("Members").Select
    
    Lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
    
    Rows("15:" & Lastrow).Sort Key1:=Range("A15"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    
    Range("H20").Select
    
End Sub

 

[MisterMo]

AlphaFrog & Simon Lloyd

Many THANKS for your time and efforts. Simon Lloyd, yours was the first I used and it worked wonderfully! Not being one to fix what ain't broke that's the code I used.

I apologize for taking this long to get back to you, but working on this spreadsheet is an on again-off again type of thing.

I hope I get this great of advice in the future (as I know I WILL need HELP!).

Be well,

Mo