Automate worksheet name VBA

[Utterly Bamboozled]

Hi, could someone help me out in understanding why the below code doesn't work please?

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.

 

[Giordano Bruno]

What is that line of code supposed to do?

 

[Utterly Bamboozled]

That line is supposed to paste the values of the other sheet.

Basically what I'm trying to do is create a copy of 'Timesheet'!, rename it using C6 (if possible), maintain the formatting but paste only the values. I will then export this sheet to a different model.

It was actually suggested by AlphaFrog but I can't seem to get in touch. Any assistance would be appreciated.

Regards, UB

 

[Giordano Bruno]

Something like this code may get you past the current problem. Sheet3 is the source sheet here and ActiveSheet is the target.

Dim FirstRow As Long
Dim FirstCol As Long

With Sheet3.UsedRange
FirstRow = .Rows(1).Row
FirstCol = .Columns(1).Column
.Copy
End With

ActiveSheet.Cells(FirstRow, FirstCol).PasteSpecial Paste:=xlValues

Hope that helps.