Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 86
Default Copy sheet name to same cell on each sheet, 50 sheets; run code onany worhseet beforedoubleclick

I'm using Excel 2000 (yes ancient, I know).

I have a workbook with about 50 worksheets, I would like to copy the
name of each worksheet to the A5 cell of each sheet, and if possible
make it large bold text. Is there a way to automate that using a macro
so I don't have to do it manually?

Also I have a list of worksheet names in the A column, I wrote a simple
subroutine (below) to open a worksheet when the matching text is
double-clicked. Is there a way to make this code work on all 50 sheets
without copying the code to each worksheet manually?

I am not too fluent with VBA, thanks!

Mike

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim strSheetName As String
strSheetName = Trim(Target.Value)
If Trim(Target.Value) = "" Then Exit Sub
Worksheets(strSheetName).Activate
End Sub
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 86
Default SOLVED: Copy sheet name to same cell on each sheet, 50 sheets; runcode on any worhseet beforedoubleclick

On 4/3/2016 3:41 PM, Mike S wrote:
I'm using Excel 2000 (yes ancient, I know).

I have a workbook with about 50 worksheets, I would like to copy the
name of each worksheet to the A5 cell of each sheet, and if possible
make it large bold text. Is there a way to automate that using a macro
so I don't have to do it manually?

Also I have a list of worksheet names in the A column, I wrote a simple
subroutine (below) to open a worksheet when the matching text is
double-clicked. Is there a way to make this code work on all 50 sheets
without copying the code to each worksheet manually?

I am not too fluent with VBA, thanks!

Mike

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim strSheetName As String
strSheetName = Trim(Target.Value)
If Trim(Target.Value) = "" Then Exit Sub
Worksheets(strSheetName).Activate
End Sub


I found these pages
https://support.microsoft.com/en-us/kb/142126
http://analysistabs.com/excel-vba/wr...orksheet-cell/

and came up with this code.

Sub WorksheetLoop()
Dim WS_Count As Long, n As Long
On Error GoTo errorhandler
WS_Count = ActiveWorkbook.Worksheets.Count
For n = 1 To WS_Count
Worksheets(Worksheets(n).Name).Activate
DoEvents
Worksheets(n).Range("A5").Value = Worksheets(n).Name
Worksheets(n).Range("A5").Font.Bold = True
Worksheets(n).Range("A5").Font.Size = 12
DoEvents
Next
Exit Sub
'
errorhandler:
MsgBox Err.Number & " " & Err.Description
End Sub

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default SOLVED: Copy sheet name to same cell on each sheet, 50 sheets; run code on any worhseet beforedoubleclick

Perhaps simply...

Sub Insert_Sheetname()
Dim n&
On Error GoTo errorhandler
For n = 1 To ActiveWorkbook.Sheets.Count
With Sheets(n).Range("A5")
.Value = Sheets(n).Name: .Font.Bold = True: .Font.Size = 12
End With
Next

NormalExit:
Exit Sub

errorhandler:
MsgBox Err.Number & " " & Err.Description
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 86
Default SOLVED: Copy sheet name to same cell on each sheet, 50 sheets;run code on any worhseet beforedoubleclick

On 4/3/2016 5:45 PM, GS wrote:
Perhaps simply...

Sub Insert_Sheetname()
Dim n&
On Error GoTo errorhandler
For n = 1 To ActiveWorkbook.Sheets.Count
With Sheets(n).Range("A5")
.Value = Sheets(n).Name: .Font.Bold = True: .Font.Size = 12
End With
Next

NormalExit:
Exit Sub

errorhandler:
MsgBox Err.Number & " " & Err.Description
End Sub

Yes much cleaner, thanks.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Unhide sheet, copy and rename new sheets from list, rehide sheet Howard Excel Programming 9 October 12th 13 02:59 PM
How to: copy value from active sheet to all other sheets with VBA (in desired cell) DVAL Excel Programming 3 December 15th 10 02:38 AM
Copy and paste same cell from a number of sheets into a new sheet Oliver Burdekin Excel Programming 3 January 8th 10 07:29 PM
Copy Sheet to new Sheet and clear cells on original sheets Boiler-Todd Excel Discussion (Misc queries) 7 September 23rd 09 10:02 PM
copy and paste from different sheets into one sheet using a VB code reena Excel Programming 2 August 5th 03 02:49 PM


All times are GMT +1. The time now is 01:05 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"