Automaticaly select range


D

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:D").EntireColumn.AutoFit
Columns("D: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.
 
Ad

Advertisements

D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top