Automaticaly select range

[Decreenisi]

Dear All,

I have the following macro attached to a button. With regard to the
select aspect, the range will change from time to time. So I either
need to put the range ie A1:R54, in a predetermined cell, say A1 all
the time. Otherwise I need some code as part of the macro, that will
give me a box to enter the range, once the button is pressed.

Range("B594:K601").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D").EntireColumn.AutoFit
Columns("D").EntireColumn.AutoFit
Columns("E:E").ColumnWidth = 8.29
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").ColumnWidth = 8.57
Columns("H:H").EntireColumn.AutoFit
Columns("I:I").EntireColumn.AutoFit
Range("A1:A8").Select
Application.CutCopyMode = False
Selection.NumberFormat = "d-mmm-yy"
End Sub


Thanks.

 

[Don Guillett]

Does this help?

Sub getrange()
fr = InputBox("Enter first cell")
lr = InputBox("Enter last cell")
Range(fr & ":" & lr).Copy
Workbooks.Add
Range("a1").PasteSpecial Paste:=xlPasteValues
Columns("A:I").AutoFit
Columns("g").ColumnWidth = 8.57
'may need to chg next line
Range("A1:A8").NumberFormat = "d-mmm-yy"
End Sub