text vs numbers copying a cell

[bhsoundman]

Hi All,

This should be a simple question, but apparently I'm a moron.

This script is intended to copy a worksheet, rename it & the copy a cell value (calculated) from the first sheet to the second sheet.

- Creating the worksheet = no problem
- Renaming that sheet to a calculated name = no problem
- Copying and pasting a cell value if that cell value is text = no problem
- Copying and pasting a cell value if that cell value is numeric = problem in line 14

Can someone please set me straight?

Thanks in advance!

Sub MakeCallSheet()

Dim WkSht As Worksheet

SourceData = Sheets("Crew").Columns(ActiveCell.Column).Rows(8).Value
SourceData2 = Sheets("Crew").Columns(ActiveCell.Column)

Set WkSht = Nothing
On Error Resume Next
Set WkSht = ActiveWorkbook.Worksheets(SourceData)
On Error GoTo 0
 
If WkSht Is Nothing Then
Worksheets("Call Sheet").Copy after:=Worksheets("Crew")
ActiveSheet.Name = SourceData

End If

Worksheets("Crew").Select
Sheets(SourceData).Range("F8") = Sheets("Crew").Columns(ActiveCell.Column).Rows(8).Value

Sheets(SourceData).Select
End Sub

 

[RoryA]

Try this version:

Sub MakeCallSheet()
   Dim CrewSheet As Worksheet
   Set CrewSheet = Sheets("Crew")
   
   Dim SourceData As String
   SourceData = CrewSheet.Cells(8, ActiveCell.Column).Value
   
   Dim WkSht As Worksheet
   On Error Resume Next
   Set WkSht = ActiveWorkbook.Worksheets(SourceData)
   On Error GoTo 0
    
   If WkSht Is Nothing Then
      Worksheets("Call Sheet").Copy after:=CrewSheet
      Set WkSht = ActiveSheet
      WkSht.Name = SourceData
   End If
   
   WkSht.Range("F8").Value = CrewSheet.Cells(8, ActiveCell.Column).Value
   WkSht.Select
End Sub

 

[bhsoundman]

Perfect RoryA! Thank you so much

 

[RoryA]

Glad we could help.

By the way, the reason for the problem was that you hadn't declared SourceData as a String. That meant that if the value was numeric, it was being stored as an actual number value. When you try and use a number value as an index in the Sheets collection, it's looking for the sheet at that position in the workbook, not a sheet by that name. So, for example, if SourceData had a value of 18, it's looking for the 18th sheet (from the left) not a sheet called "18". If you make SourceData a String, all values are treated as text regardless, so it ends up looking for Sheets("18") - i.e. a sheet named "18" - rather than Sheets(18).
Does that make sense?

 

[bhsoundman]

Glad we could help.

By the way, the reason for the problem was that you hadn't declared SourceData as a String. That meant that if the value was numeric, it was being stored as an actual number value. When you try and use a number value as an index in the Sheets collection, it's looking for the sheet at that position in the workbook, not a sheet by that name. So, for example, if SourceData had a value of 18, it's looking for the 18th sheet (from the left) not a sheet called "18". If you make SourceData a String, all values are treated as text regardless, so it ends up looking for Sheets("18") - i.e. a sheet named "18" - rather than Sheets(18).
Does that make sense?

Thanks for the quick lesson. I knew it had something to do with declarations, but using the "figure it out as I go" method is slow and not very efficient.