SEQUENTIAL WORKSHEET CREATION FOR PRINT FORMAT

jimmy2468

New Member
Joined
Oct 10, 2006
Messages
9
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]
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Watch MrExcel Video

Forum statistics

Threads
1,111,908
Messages
5,541,531
Members
410,547
Latest member
htran4
Top