Mark,
So you want create a new worksheet ?
Set Targetsht = ActiveWorkbook.Worksheets.Add
Targetsht.Name="Whatever"

NickHK

"markx" wrote in message
...
Thank you both for your comments, but I think I'm not as good in VBA
programming as you maybe think... and are not quite sure how to interpret
your input.
I'll try to put in place the solution proposed by Dutch and get back to
you
later, but as far as I can understand it, it will only work if the sheets
ARE ALREADY CREATED.

So, how should I proceed if I there are no other worksheets in place (the
only one being the "source" sheet)? (just to recall: for the time being
the
code creates normally all the sheets it needs and then puts all the
relevant
data in place UNLESS the criteria/variables are "numeric"). What would be
the best solution to overcome this limitation?

Thanks once again for your help (and sorry if you already answered the
question, but I was *unable* to get it:-D)
Mark



"NickHK" wrote in message
...
mark,
I assume you are attempting to reference e.g
ActiveWorkbook.Worksheets(1)

but Curr*entCellValue="1" (a string), not 1 (a number).

So unless you have a sheet with the name of "1" on its tab, it will
fail.

NickHK

"markx" wrote in message
...
Hello there,

I've submitted this problem yesterday to the
"excel.worksheets.functions"
forum, but received only partial (although very helpful) response.
Could
you
help me with the following issue (just to be sure how it works...)?
--------------
Set Targetsht = ActiveWorkbook.Worksheets(Curr*entCellValue)
'note: using CurrentCell.value gave me an error if the value was
numeric

Does any of you have an idea how to modify the code in order to include
also
numeric values as possible variables?
--------------
Many thanks!


The entire code is as follows:


Sub CopyRowsToSheets()
'copy rows to worksheets based on value in column A
'assume the worksheet name to paste to is the value in Col A
Dim CurrentCell As Range
Dim SourceRow As Range
Dim Targetsht As Worksheet
Dim TargetRow As Long
Dim CurrentCellValue As String

'start with cell A2 on Sheet1
Set CurrentCell = Worksheets("sheet1").Cells(2, 1) 'row 2 column 1

Do While Not IsEmpty(CurrentCell)
CurrentCellValue = CurrentCell.Value
Set SourceRow = CurrentCell.EntireRow

'Check if worksheet exists
On Error Resume Next
Testwksht = Worksheets(CurrentCellValue).N*ame
If Err.Number = 0 Then
'MsgBox CurrentCellValue & " worksheet Exists"
Else
MsgBox "Adding a new worksheet for " & CurrentCellValue
Worksheets.Add.Name = CurrentCellValue
End If

On Error GoTo 0 'reset on error to trap errors again

Set Targetsht = ActiveWorkbook.Worksheets(Curr*entCellValue)
'note: using CurrentCell.value gave me an error if the value was
numeric

' Find next blank row in Targetsht - check using Column A
TargetRow = Targetsht.Cells(Rows.Count, 1).End(xlUp).Row 1
SourceRow.Copy Destination:=Targetsht.Cells(T*argetRow, 1)

'do the next cell
Set CurrentCell = CurrentCell.Offset(1, 0)
Loop
End Sub