SEQUENTIAL WORKSHEET CREATION FOR PRINT FORMAT

[jimmy2468]

From a long list of data (on "Sheet1" of the workbook), The following code will prompt a user to define a start point, and an end point.

The macro then takes that range of info and arranges it in an acceptable format for printing on "Sheet2"

I would like to modify this to add 2 features:

1) To prompt the user to "NAME SHEET" after they enter the start and end points. I would like this name to appear on the sheet previously named "Sheet2"

2) I would like to create a new sheet every time the macro is run (via push button)

So in the end state:

Button is pushed.
User is prompted for start row
User is prompted for end row.
User is prompted for Sheet Name

Result: A new sheet is created named by what is entered into the Sheet Name.

If button is pushed again, ANOTHER sheet is created (the first one remains)

Here is the code:
Sub PreparePrint()
Dim lrow As Long, lTargetRow As Long, lTargetCol As Integer, iNRowsPrint As Integer
Const MaxCol As Integer = 9, MaxPageRow As Integer = 52
Dim StartRow As Integer, EndRow As Integer
Dim a, b As Integer


a = InputBox("ENTER START ROW")
StartRow = a

b = InputBox("ENTER END ROW")
EndRow = b

Worksheets("Sheet2").UsedRange.Clear
For lrow = StartRow To EndRow Step MaxPageRow
lTargetRow = 1 + MaxPageRow * ((lrow - StartRow) \ (MaxPageRow * MaxCol))
lTargetCol = 1 + ((lrow - StartRow) Mod (MaxPageRow * MaxCol)) \ MaxPageRow
iNRowsPrint = IIf(MaxPageRow <= EndRow - lrow, MaxPageRow, 1 + EndRow - lrow)
Worksheets("Sheet2").Cells(lTargetRow, lTargetCol).Resize(iNRowsPrint).Value = _
Worksheets("Sheet1").Cells(lrow, 1).Resize(iNRowsPrint).Value
Next
End Sub




THANKS!


[/code]