"currentcell.value" not working when numeric?

[markx]

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

 

[Dutch Gemini]

The 'Worksheets()' function allows you to select a worksheet by Name or bu
Number. In this last case, the number may *NOT* be higher than the number of
sheets otherwise Excel triggers an error.

If you do have worksheets that are also labelled as numbers, then use this
trick:

Set Targetsht = ActiveWorkbook.Worksheets("" & Curr­entCellValue)

This will transform the numerical value into a string and allow you to
locate the sheet.

Tip: always use the "Err" object to trap errors

On Error Resume Next
Err.Clear
Set Targetsht = ActiveWorkbook.Worksheets("" & Curr­entCellValue)
If Err.Number <> 0 Then
MsgBox "Your error message here"
Else
{...whatever...}
End If

Dutch

 

[NickHK]

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]

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]

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

NickHK

 

[markx]

Thanks Nick,

Yes, I would like to have all the worksheets created, but with the names
that are taken from the relevant column from the "source" page.
It means that if in our column we have the following categories: "Apples",
"Bananas", "Pears", then I would like to have 3 new pages created with the
names "Apples", "Bananas", "Pears" and then filled with the corresponding
fields.
My problem is that if the labels (categories) are not names (like "Apples",
"Bananas", "Pears"), but numbers (like "1", "5", "123"), the code gives me
the error in the line

Set Targetsht = ActiveWorkbook.Worksheets(Curr­entCellValue)
<for the full code see below>

My goal would be to find a solution to modify the code in such a way that it
will overcome this limitation and will add/fill the pages with the necessary
data irrespective of the variable type (i.e. either text or numeric).

Thanks again for all your feedback!
Mark

 

[NickHK]

Mark,
Code works for me.
What error do you get ?

NickHK

Thanks Nick,

Yes, I would like to have all the worksheets created, but with the names
that are taken from the relevant column from the "source" page.
It means that if in our column we have the following categories: "Apples",
"Bananas", "Pears", then I would like to have 3 new pages created with the
names "Apples", "Bananas", "Pears" and then filled with the corresponding
fields.
My problem is that if the labels (categories) are not names (like "Apples",
"Bananas", "Pears"), but numbers (like "1", "5", "123"), the code gives me
the error in the line

Set Targetsht = ActiveWorkbook.Worksheets(Curr­entCellValue)
<for the full code see below>

My goal would be to find a solution to modify the code in such a way that it
will overcome this limitation and will add/fill the pages with the necessary
data irrespective of the variable type (i.e. either text or numeric).

Thanks again for all your feedback!
Mark

------------------ CUT ----------------------

 

[Dutch Gemini]

In your code I found this statement:

{...}
Else
MsgBox "Adding a new worksheet for " & CurrentCellValue
Worksheets.Add.Name = CurrentCellValue
End If
{...}

It is incorrect. It should be

Dim wNewSheet As Worksheet
{...}
Else
MsgBox "Adding a new worksheet for " & CurrentCellValue
Set wNewSheet = Workbook.Sheets.Add ' {additional stements here}
With wNewSheet
.Name = "" & CurrentCellValue ' assign "Title" of sheet here
' {other statements here, for instance using .Move to move it at the
end}
End With
End If
{...}

I would also:

Dim CurrentCellValue As Variant

instead of

Dim CurrentCellValue As String

to take care of Excel's habit of self-determining the cell's data type

Dutch