Hello Doug,
It is perfect. Thanks a lot. Thats what I wanted.
Rashid Khan
"Doug Glancy" wrote in message
...
Rashid,

I believe this does what you want - allows you to name a new sheet to be
added and then unsnakes to that sheet. If you enter a duplicate sheet
name
then you get a message and the macro exits. I hope this is what you want!

Sub Test2()

Dim first_row As Long, last_row As Long

Dim active_sheet As Worksheet
Dim mySheet As Worksheet
Dim myName As String
myName = Trim(InputBox("Enter the sheet name", _
"supply sheetname"))

Set active_sheet = ActiveSheet
Set mySheet = Worksheets.Add
On Error Resume Next
mySheet.Name = myName
If Err < 0 Then
MsgBox "That sheet name already exists"
Application.DisplayAlerts = False
mySheet.Delete
Application.DisplayAlerts = True
Exit Sub
End If
On Error GoTo 0

active_sheet.Activate

first_row = Selection.Rows(1).Row
last_row = first_row
While last_row < Selection.Rows(Selection.Rows.Count).Row
last_row = Application.WorksheetFunction.Min(ActiveSheet.Rang e("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

mySheet.Activate

End Sub

hth,

Doug Glancy

"Rashid Khan" wrote in message
...
Hello David,
I have copied the complete macro here for you to have a look.
It gives Run-time error '91' - Object variable or With block variable
not
set.
It does not even copy the data on the Sheet2.

My requirement is to have the selection to be copied to the Sheet name
provided by me. The sheet should be created by the name which is
supplied
in the Input Box.

Hope I am clear now.
Any help would be appreciated
Sub Test2()

Dim first_row As Long, last_row As Long

Dim mySheet As Worksheet
Dim myName As String
myName = Trim(InputBox("Enter the sheet name", _
"supply sheetname", ActiveSheet.Name))
MsgBox 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




"David McRitchie" wrote in message
...
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