Renaming worksheet in VBA

[Crocdundee]

Hi I import a csv file each day in excel,and the worksheet has a different name each day.
I am using sheet1 as the worksheet in a macro.
How can I change the unknown worksheet to Sheet1 in VBA
This is the name of the worksheet I import "worksheet_Thursday, 12 May"
However it changes each day

-----------------------------------------
The way I see it,is
(1) use vba to capture the worksheet in a variable....the use the variable ??
(2) Rename it in vba ???
------------------------------------------------------
Any Help would be appreciated
Thank You
Graham

 

[Blade Hunter]

Activesheet.name = "Sheet1"

Or in your host macro reference the sheet index (probably 1)

Sheets(1). instead of Sheets("Sheet1").

Cheers

Dan

 

[brettdj]

Graham,

If you know the sheet's position post import then you can change it simply by running code, say for the leftmost sheet (ie position 1)

ThisWorkbook.Sheets(1).Name = "Sheet1"

How do you do your import? VBA or manually?

Dave

 

[Crocdundee]

Thanks Dave I will try that code and let u know.
I Import Manually
it is a different name each day as i use Python code and it generates a file each day with todate in the file name

Grah

 

[Crocdundee]

Dave The code dos'nt work for this worksheet
I also tried it on other excel files to no avail
Regards
Graham

 

[John Davis]

Hi I import a csv file each day in excel,and the worksheet has a different name each day.
I am using sheet1 as the worksheet in a macro.
How can I change the unknown worksheet to Sheet1 in VBA
This is the name of the worksheet I import "worksheet_Thursday, 12 May"
However it changes each day

-----------------------------------------
The way I see it,is
(1) use vba to capture the worksheet in a variable....the use the variable ??
(2) Rename it in vba ???
------------------------------------------------------
Any Help would be appreciated
Thank You
Graham

Maybe:

Sheets("worksheet_Thursday, 12 May").Name = "Sheet1"

Or

Activesheet.name = "Sheet1"

 

[Crocdundee]

Thanks very much, The Active code worked fine

Appreciated your effort

Graham

 

[brettdj]

Graham,

You may want to consider automating the input as well. The code below should be hosted in the workbook where the renaming takes place (set by ThisWorkbook - possibly why the earlier code failed if you places it elsewhere). The code checks for an existing Sheet1 before the renaming

Cheers

Dave

Sub ReName()
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets("Sheet1")
    On Error GoTo 0
    If ws Is Nothing Then
        ThisWorkbook.ActiveSheet.Name = "Sheet1"
    Else
        MsgBox "Sheet1 already exists", vbCritical
    End If
End Sub