VBA Question

[Utterly Bamboozled]

Could someone please tell me why this code keeps crashing Excel??

'
Sheets("Timesheet").Select
Sheets("Timesheet").Copy Before:=Sheets(5)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Timesheet (2)").Select
Sheets("Timesheet (2)").Name = Range("C6").Value
Range("C6:E6").Select
Application.CutCopyMode = False
End Sub

Thanks!

 

[Datsmart]

What do you have in cell C6?
Your code is trying to rename the worksheet to that value.
Do you have characters that may not be legal for a sheetname?

 

[AlphaFrog]

Try something like this...

Sub test()

    Sheets("Timesheet").Copy Before:=Sheets(5)
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    
    On Error Resume Next
        ActiveSheet.Name = Range("C6").Text
    On Error GoTo 0
    
    If ActiveSheet.Name <> Range("C6").Text Then MsgBox "Couldn't rename the sheet as " & Range("C6").Text
    
    ActiveSheet.Range("C6:E6").Select

End Sub

 

[Utterly Bamboozled]

Thanks guys,

Cell C6 is actually a merged cell (C6:E6), but the code was working previously and all of a sudden decided to crash. Strange.

AlphaFrog, I've attempted to use your suggestion as follows:

Sub Export()
'
' Sheets("Timesheet").Copy Before:=Sheets(5)
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

On Error Resume Next
ActiveSheet.Name = Range("C6").Text
On Error GoTo 0

If ActiveSheet.Name <> Range("C6").Text Then MsgBox "Couldn't rename the sheet as " & Range("C6").Text

ActiveSheet.Range("C6:E6").Select
End Sub

I get a runtime error 1004 - Application defined or Object defined error. When I debug it highlights ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

Thanks for your help.

 

[AlphaFrog]

I don't know why you are getting an error. I can't get it to error for me no matter what I try.

What version of Excel do you have?

 

[AlphaFrog]

If your worksheet is Protected, you could get that error. You must Unprotect the worksheet first...

Sub Export()
'
    Sheets("Timesheet").Copy Before:=Sheets(5)
    
    [COLOR="Red"]ActiveSheet.Unprotect Password:="Secret"[/COLOR]
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    
    On Error Resume Next
    ActiveSheet.Name = Range("C6").Text
    On Error GoTo 0
    
    If ActiveSheet.Name <> Range("C6").Text Then MsgBox "Couldn't rename the sheet as " & Range("C6").Text
    
    ActiveSheet.Range("C6:E6").Select

End Sub

 

[Utterly Bamboozled]

That's strange... I'm using 2007. I'll continue to tinker and get back to with the result.

Thanks again for your help

 

[Utterly Bamboozled]

Thanks AlphaFrog, that's exactly what is was!

 

[Utterly Bamboozled]

Is it possible to add a line of code to remove all named ranges in the copied worksheet? The reason is when I go to export this sheet to a different workbook (and several versions of the same copied sheet will be exported) Excel says the name already exists on the destination worksheet. Then I end up having a large number of named ranges in my names manager that are linked to an external workbook.

Thanks for your help.

 

[Datsmart]

Sub DeleteNamedRanges()
Dim i As Integer
    On Error Resume Next
    For i = 1 To Names.Count
        Names(i).Delete
    Next i
End Sub