Range error question...

[rarogersonkf]

The following code causes the error...
Run-time error '1004'
Application-defined or object-defined error.

Can someone explain what's wrong? (I figure that the new worksheet is not in the scope of the called subs but I'm not sure)

Private Sub CommandButton1_Click()

AddNewSheet

End Sub

Private Sub AddNewSheet()

Dim Newname As String
Newname = InputBox("Name for new worksheet?")
If Newname <> "" Then
Sheets.Add Type:=xlWorksheet
ActiveSheet.Name = Newname
End If
MakeHeader

End Sub

Private Sub MakeHeader()

Range("A1").Activate
Range("A1").Value = "Data refreshed..: " + Date$

End Sub

Thanks in advance for any insight.

Rob

 

[PHV]

And this ?
ActiveSheet.Range("A1").Value = "Data refreshed..: " & Date$

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rarogersonkf]

Worked lika a charm. Thanks...

I even was able to make use of With/End With in Makeheaders

Public Sub MakeHeaders()

With ActiveSheet
.Range("A1").Activate
.Range("A1").Value = "Data refreshed..: " + Date$
.Range("A1").Font.Size = 10
.Range("A1").Font.Bold = True

.Range("A3").Activate
.Range("A3").ColumnWidth = 5.71
.Range("A3").Value = "Dept"
.Range("A3").Font.Size = 10
.Range("A3").Font.Bold = True
.Range("A3").WrapText = True

End With

End Sub

Thanks again...

Rob

 

[SkipVought]

better use of With...End With

Public Sub MakeHeaders()

    With ActiveSheet
        With .Range("A1")   'no need to ACTIVATE
            .Value = "Data refreshed..: " & Date$
            With .Font
                .Size = 10
                .Bold = True
            End With
        End With
        
        With .Range("A3")   'no need to ACTIVATE
            .ColumnWidth = 5.71
            .Value = "Dept"
            With .Font
                .Size = 10
                .Bold = True
            End With
            .WrapText = True
        End With
    End With

End Sub

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}