Rename sheets

[ilyaskazi]

I want to import all the sheets in my workbook_2 of workbook_1.

Sheet names of workbook_1 are always different.

After importing all the sheets from workbook_1, rename all the imported sheets with its name appending with "1" (for eg; MySheet -> MySheet1 and so on)

[johnske]

Hi ilyaskazi,

Put the two books in a folder, paste this code in the book where you want the sheets imported to and run the code (Note: this is not restricted to importing sheets from one book - it will import all the sheets from all the books that you put in the folder).

Note that if you have any links in your worksheets, those links will now point to the original workbook[vba]Option Explicit


Sub AmalgamateBooks()
Dim ThisBook As Workbook, OtherBook As Workbook, SheetName$, i&, N&
Set ThisBook = ActiveWorkbook
Application.ScreenUpdating = False
With Application.FileSearch
.LookIn = ActiveWorkbook.Path
.FileName = "*.xls"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
If .FoundFiles(i) <> ThisWorkbook.FullName Then
Application.Workbooks.Open(.FoundFiles(i)).Activate
Set OtherBook = ActiveWorkbook
With OtherBook
For N = 1 To Sheets.Count
Sheets(N).Activate
SheetName = ActiveSheet.Name
Cells.Copy
With ThisBook
.Activate
Worksheets.Add After:=Sheets(Sheets.Count)
With .ActiveSheet
.Paste
.Name = SheetName & "1"
[A1].Select
End With
Application.CutCopyMode = False
End With
.Activate
Next N
End With
With OtherBook
.Close False
End With
End If
Next i
End If
End With
Sheet1.Activate
End Sub

HTH,
John

[ilyaskazi]

thankyou john,

i m checking your codes. after renaming with digit as "1", these sheets hv to rename with its original name after completion of its work in this workbook. Means remove "1" from all the sheets name.

Here different execution function required.

[johnske]

Hi,

When you need to remove the "1", just use

ActiveSheet.Name = Left(ActiveSheet.Name, Len(ActiveSheet.Name) - 1)

Regards,
John

[ilyaskazi]

Suppose I do not want to import any sheets from any excel file
but I need to rename all sheets containing in my activeworkbook.

for eg.: "PRTP" => "PRTP1", "CHET" => "CHET1", "ILYS" => "ILYS1"

Workbook may contain sheets in any numbers with any names.

I hv tried solving like this...


Sub Rename_Sheet1()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Name = ws.Name & "1"
Next ws
End Sub

...but no output came.

[Bob Phillips]

That code worked fine for me.

[johnske]

Try this variation


Option Explicit

Sub Rename_Sheet1()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Name = ws.Name & "1"
Next ws
End Sub

[ilyaskazi]

Then why i m not geting the output???

I m using office xp-2003. Does it matter?

[johnske]

Then why i m not geting the output???

I m using office xp-2003. Does it matter?

2003 should make no difference - :dunno both versions of the code should work :dunno

[ilyaskazi]

ok now its working correct.

To remove "1" from all worksheets, this code does not work..


ActiveSheet.Name = Left(ActiveSheet.Name, Len(ActiveSheet.Name) - 1)

how to rename all???

[Bob Phillips]

this code does not work..

ActiveSheet.Name = Left(ActiveSheet.Name, Len(ActiveSheet.Name) - 1)

Afraid to say, but it works for me.

You might want to make it generic to cater for Sheet11 etc.



Dim sNum As String
Dim iPos As Long
Dim i As Long
With ActiveSheet
For i = Len(.Name) To 1 Step -1
If Not IsNumeric(Mid(.Name, i, 1)) Then
iPos = i
Exit For
End If
Next i
If iPos <> Len(.Name) Then
.Name = Left(ActiveSheet.Name, iPos)
End If
End With

[ilyaskazi]

hth,

SHEET NAMES: "ILYS1", "RAJ2", "KANT1", "FRDSIS2"

I want to remove last digit "1" only and not "2" from all the worksheets in the workbook.

Also plz let me know whether it works perfectly for startup file "PERSONAL.xls". My macro is in this file only and not in any other workbook.

-----------------------------------------x--------x--------------------------------

next question: rename all worksheets with its orginal name appending with "1"
-------------

renaming with "1" works only with the workbook for our file.
i m writing code in my "PERSONAL.xls" --the startup file of excel
Try this and let me know whether now it works??

i hv checked here, and the only reason i found unable to excute this code.

[Bob Phillips]

SHEET NAMES: "ILYS1", "RAJ2", "KANT1", "FRDSIS2"

I want to remove last digit "1" only and not "2" from all the worksheets in the workbook.



With ActiveSheet
If Right(.Name, 1) = "1" Then
.Name = Left(.Name, Len(.Name) - 1)
End If
End With

[ilyaskazi]

singly perfect, but how to do if to rename for all the worksheets if found??

[Bob Phillips]

singly perfect, but how to do if to rename for all the worksheets if found??



For Each sh In ActiveWorkbook.Worksheets
With sh
If Right(.Name, 1) = "1" Then
.Name = Left(.Name, Len(.Name) - 1)
End If
End With
Next sh

[ilyaskazi]

dat's good and totally perfect now. Everything is solved.

Thankyou very much for your precious time.

regards,
ilyas kazi