You should be identifying the line that you get the error on,
but Subscript out of range is the type of error that you
would get for sheet not found. Make sure that you
have not introduced spaces at the end of the sheet name.
Do you have something that could be interpreted by
Excel as a number. By the way if you have a suggestion
you can supply a default sheetname to the
InputBox.

myName = TRIM(InputBox("Enter the sheet name", _
"supply sheetname", activesheet.name))
msgbox myname & "<-- verify"

InputBox and MsgBox
http://www.mvps.org/dmcritchie/excel/inputbox.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Rashid Khan" wrote in message ...
Hi Bernie,
It does not work for a single sheet also.
It gives the following error:
Run Time Error 9
Subscript out of range

What is missing?
Rashid

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Rashid,

This will work on the active sheet, and ask for the name of the
destination
sheet. Note that there is no error checking on the input of the sheet
name.

HTH,
Bernie
MS Excel MVP

Sub Test2()

Dim first_row As Long, last_row As Long

Dim mySheet As Worksheet
Dim myName As String

myName = InputBox("Enter the sheet name")
Set mySheet = Worksheets(myName)

first_row = Selection.Rows(1).Row
last_row = first_row
While last_row < Selection.Rows(Selection.Rows.Count).Row
last_row = Application.WorksheetFunction.Min(Sheet1.Range("A" & _
first_row).End(xlDown).Row, _
Selection.Rows(Selection.Rows.Count).Row)
ActiveSheet.Range("A" & first_row & ":A" & last_row).Copy _
Destination:=mySheet.Range("A" & Rows.Count).End(xlUp)(2)
ActiveSheet.Range("B" & first_row & ":B" & last_row).Copy _
Destination:=mySheet.Range("A" & Rows.Count).End(xlUp)(2)
first_row = last_row + 2
Wend

End Sub


"Rashid Khan" wrote in message
...
Note: I had already posted it but no reply was received so I am
reposting
under a different subject

Hello All,
I am using Office XP and the following macro was posted by Mr. Doug
Glancy
to my previous question about Snake Columns. The macro works perfect
but
every time I have to make a new Workbook

I wish to change this to work for Sheets in the same workbook. At
present
it is working for only Sheet2 of a new Workbook only. I tried to
change
Sheet3 Name to Sheet2 but it works only once. How can I amend it to work
in
the same Sheet for other subsequent Sheets?

The macro should stop and ask for the new Sheet name prior to copying
the
snake column.


Sub test()

Dim first_row As Long, last_row As Long

first_row = Selection.Rows(1).Row
last_row = first_row
While last_row < Selection.Rows(Selection.Rows.Count).Row
last_row = Application.WorksheetFunction.Min(Sheet1.Range("A" & _
first_row).End(xlDown).Row, _
Selection.Rows(Selection.Rows.Count).Row)
Sheet1.Range("A" & first_row & ":A" & last_row).Copy _
Destination:=Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1,
0)
Sheet1.Range("B" & first_row & ":B" & last_row).Copy _
Destination:=Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1,
0)
first_row = last_row + 2
Wend

End Sub