macro help pls

[Guest]

Hello everybody. I need help to modify the macro below
Range("A7").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, ActiveCell.End(xlToRight)).Select
Selection.Clear
Range("A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("A7:A41").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

In Range("A7:A41").Select; instead of A41 i want to change it A(value in
B2). The value in B2 changes. So i would like to erase the existing
formulae from A7: ,and copy the formulae.selection from A7:A(B2).
Any suggestions.
Thank you to all
With regards
Sridhar

 

[Guest]

Replace 'Range("A7:A41")' in your macro with
'Range("a7").resize(range("b2").value-6,1).Select'

 

[papou]

Hello Sridhar

Range("A7:A" & [B2]).Select

But note that selection of cells is not necessary.
You could use somtehing like:
Range("A6", Range("A6").End(xlToRight)).Copy Range("A7")
Since you only need to specify the first cell receiving the copy

HTH
Cordially
Pascal

 

[Guest]

Thanks alot Mr. Pascal for your micro reply and for your suggestion.
It works for me and that is what i want.
regards
Sridhar

Hello Sridhar

Range("A7:A" & [B2]).Select

But note that selection of cells is not necessary.
You could use somtehing like:
Range("A6", Range("A6").End(xlToRight)).Copy Range("A7")
Since you only need to specify the first cell receiving the copy

HTH
Cordially
Pascal

Hello everybody. I need help to modify the macro below
Range("A7").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, ActiveCell.End(xlToRight)).Select
Selection.Clear
Range("A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("A7:A41").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

In Range("A7:A41").Select; instead of A41 i want to change it A(value in
B2). The value in B2 changes. So i would like to erase the existing
formulae from A7: ,and copy the formulae.selection from A7:A(B2).
Any suggestions.
Thank you to all
With regards
Sridhar