Help running VB code

Hi,

The code below was sent to me by a nice guy named Jim, but I've never worked with code in Excel before, and my book doesn't help me understand how to run it. Essentially, I want to convert some rows to columns so that each row (column after transposing) will be on a separate page. Jim wrote some code that does this, which transposes rows to become columns, with one column for each page. When I try to run the program, it asks "Enter the number of rows per page", then asks to "enter the start row". Per the code, I think I understand what to type for the "Enter the number of rows...", but I don't know what to type to "enter the start row". I've tried A1, R1C1, and other variables, but I either get the error message "Application-defined or object-defined error" or "Select method of Range class failed". Sorry if this is a basic question, I thank anyone for the help. And thanks again to Jim! Here's the code:


'--------------------------------------------------------------------------
Sub RowsToSeparatePages()
' Transposes each row in user specified data area
' into the first column on a new worksheet.
' Assumes data starts in Column A.
' User specifies the number of rows that each
' printable page will take and the first row with data.
' A page break is inserted to force the printing of separate pages.
' Jim Cone - San Francisco, USA - Feb 16, 2005

On Error GoTo ExitProcess
Dim lngRowIncrement As Long
Dim varRowSpacing As Variant
Dim varFirstRow As Variant
Dim rngRow As Excel.Range
Dim rngActual As Excel.Range
Dim rngToMove As Excel.Range

'Get information from the user.
'Rows per page should be => than the max
'number of columns with data.
varRowSpacing = InputBox("Enter number of rows per page.", _
" Rows to Pages", "Enter here")
varRowSpacing = Abs(Val(varRowSpacing))
'If no entry then quit
If Len(varRowSpacing) = 0 Then Exit Sub
varFirstRow = InputBox("Enter the start row.", _
" Rows to Pages", "Enter here")
varFirstRow = Abs(Val(varFirstRow))
'If no entry then quit
If Len(varFirstRow) = 0 Then Exit Sub

Application.ScreenUpdating = False
'Create a copy of the active sheet.
ActiveSheet.Copy After:=ActiveSheet
Columns("A").Insert

'Find the cells on the worksheet with the data.
'Calls Function BottomRightCorner
Set rngToMove = Range(Cells(varFirstRow, 2), _
BottomRightCorner(ActiveSheet))

'Go thru each row in the data area and transpose into the first column.
For Each rngRow In rngToMove.Rows
'Find the actual cells with data
Set rngActual = Range(rngRow.Cells(1), _
rngRow.Cells(1, rngRow.Cells.Count + 1).End(xlToLeft))
rngActual.Copy
Cells(varFirstRow + lngRowIncrement, 1).PasteSpecial Transpose:=True
lngRowIncrement = lngRowIncrement + varRowSpacing
Rows(lngRowIncrement + varFirstRow).PageBreak = xlPageBreakManual
ActiveSheet.DisplayPageBreaks = False
Next 'rngRow
rngToMove.Clear
ExitProcess:

On Error Resume Next
Application.CutCopyMode = False
Cells(varFirstRow, 1).Select
Application.ScreenUpdating = True
Set rngRow = Nothing
Set rngActual = Nothing
Set rngToMove = Nothing
End Sub

'---------------------------------------------------------------
Function BottomRightCorner(ByRef objSheet As Worksheet) As Range
On Error GoTo NoCorner
Dim BottomRow As Long
Dim LastColumn As Long
If objSheet.FilterMode Then objSheet.ShowAllData
BottomRow = objSheet.Cells.Find(what:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
LastColumn = objSheet.Cells.Find(what:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
Set BottomRightCorner = objSheet.Cells(BottomRow, LastColumn)
Exit Function
NoCorner:
Beep
Set BottomRightCorner = objSheet.Cells(1, 1)
End Function
'------------------------------------------------------------------

Hi Paul,

you're almost there. Just eneter the row NUMBER where your data starts. For example if the data to be converted starts in cell D7, just enter the 7.

The following explains the code relating to your problem if you are interested:

varFirstRow = InputBox("Enter the start row.", _
" Rows to Pages", "Enter here")
---This displays the text box where you eneter your row data
---The result of what you type is stored in the varFirstRow variable

varFirstRow = Abs(Val(varFirstRow))
--- This makes sure you get a number for what you typed in
--- The pussle is if you enter "A1" the variable varFirstRow
--- would contain "A1", hence val(varFirstRow) = val(A1)
--- and this should return 1 and I'm unsure why it doesn't!
--- ABS just makes sure you get the absolute numenr, ABS(-1) and
--- ABS(1) both return 1.

Set rngToMove = Range(Cells(varFirstRow, 2), _
BottomRightCorner(ActiveSheet))
--- A further clue that a number is expected
--- Cells(varFirstRow,2) points to a cell in column 2, at the row contained
--- in the varFirstRow. The code above should have ensured this is a number
--- and in the example where you enetered A1, this would equate to
--- Cells(1,2) or cell B1.

HTH

Art

I'm sorry to post again on this, but I've never worked with VB before. Let's say I have a sheet with 5 rows and 5 columns of data. Running the code below will convert the rows to 5 columns with 5 rows, each column to be printed on a separate page. To make this happen, what do I type for my "Enter the number of rows per page" and "enter the start row"? I thought it would be "1" for number of rows, and "1" for the start row, but I get the "select method of range class failed" message. Thanks to Art and Jim who have tried to help me with what I'm sure is really simple!

Paul