Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Refering to new sheet name
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 65
Default Refering to new sheet name
I have a macro that renames a tab based on some cell contents from the same
sheet (see code below). I need to insert this routine into the beginning of
another macro that filter/copy/pastes information on the sheet based on the
same cell contents but I can't figure out how to refer to the sheet after it
has just been renamed by the first routine. I'm very clumsy with variables
but I'm guessing this is what I need. Any ideas? I'll include the second
portion of the routine as well.

This is the renaming macro:

Sub sheetname()
'
' sheetname Macro
' Macro recorded 9/10/2007 by a2holder
'

'
ActiveSheet.Unprotect
Range("G2").Select
Selection.Copy
Range("L3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("G3").Select
Application.CutCopyMode = False
Selection.Copy
Range("N3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("G4").Select
Application.CutCopyMode = False
Selection.Copy
Range("P3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("Q3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 =
"=CONCATENATE(RC[-5],RC[-4],RC[-3],RC[-2],RC[-1])"
With ActiveSheet
.Name = .Range("Q3").Value
End With
ActiveSheet.Protect
End Sub


This is the "filtering" macro:

Sub Display1()
'
' Display1 Macro
' Display analytical sheet 091406
'

'
ActiveSheet.Unprotect
Rows("7:7").Select
Selection.EntireRow.Hidden = False
Rows("6:6").Select
Selection.EntireRow.Hidden = False
Sheets("Database").Visible = True
Sheets("WORKSHEET 1").Visible = True
Sheets("WORKSHEET 2").Visible = True
Sheets("WORKSHEET 1").Select
Rows("3:6").Select
Selection.ClearContents
Range("A3").Select
Sheets("Database").Columns("A:AU").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A3"), Unique:=False
Sheets("WORKSHEET 2").Select
Range("A5:S52").Select
Selection.Copy
Sheets("Line 1").Select
Range("A8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A14:P55").Select
Application.CutCopyMode = False
Range("A14:P55").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("B6:B7"), Unique:=False
Rows("6:7").Select
Selection.EntireRow.Hidden = True
Sheets("Database").Select
Sheets("Database").Visible = False
Sheets("WORKSHEET 1").Visible = False
Sheets("WORKSHEET 2").Visible = False
Sheets("Line 1").Select
Range("C3").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub
--
n00b lookn for a handout :)
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Refering to new sheet name
Arnie: You renamed the code in the code below

With ActiveSheet
.Name = .Range("Q3").Value
End With

Just add a new variable anme

With ActiveSheet
.Name = .Range("Q3").Value
NewSheetName = .name
End With

Later refer to sheet by new name

sheets(NewSheetName).activate

or
worksheets(NewSheetName).activate

No quotes are required around NewSheetName because it is a variable.
"Arnie" wrote:

I have a macro that renames a tab based on some cell contents from the same
sheet (see code below). I need to insert this routine into the beginning of
another macro that filter/copy/pastes information on the sheet based on the
same cell contents but I can't figure out how to refer to the sheet after it
has just been renamed by the first routine. I'm very clumsy with variables
but I'm guessing this is what I need. Any ideas? I'll include the second
portion of the routine as well.

This is the renaming macro:

Sub sheetname()
'
' sheetname Macro
' Macro recorded 9/10/2007 by a2holder
'

'
ActiveSheet.Unprotect
Range("G2").Select
Selection.Copy
Range("L3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("G3").Select
Application.CutCopyMode = False
Selection.Copy
Range("N3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("G4").Select
Application.CutCopyMode = False
Selection.Copy
Range("P3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("Q3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 =
"=CONCATENATE(RC[-5],RC[-4],RC[-3],RC[-2],RC[-1])"
With ActiveSheet
.Name = .Range("Q3").Value
End With
ActiveSheet.Protect
End Sub


This is the "filtering" macro:

Sub Display1()
'
' Display1 Macro
' Display analytical sheet 091406
'

'
ActiveSheet.Unprotect
Rows("7:7").Select
Selection.EntireRow.Hidden = False
Rows("6:6").Select
Selection.EntireRow.Hidden = False
Sheets("Database").Visible = True
Sheets("WORKSHEET 1").Visible = True
Sheets("WORKSHEET 2").Visible = True
Sheets("WORKSHEET 1").Select
Rows("3:6").Select
Selection.ClearContents
Range("A3").Select
Sheets("Database").Columns("A:AU").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A3"), Unique:=False
Sheets("WORKSHEET 2").Select
Range("A5:S52").Select
Selection.Copy
Sheets("Line 1").Select
Range("A8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A14:P55").Select
Application.CutCopyMode = False
Range("A14:P55").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("B6:B7"), Unique:=False
Rows("6:7").Select
Selection.EntireRow.Hidden = True
Sheets("Database").Select
Sheets("Database").Visible = False
Sheets("WORKSHEET 1").Visible = False
Sheets("WORKSHEET 2").Visible = False
Sheets("Line 1").Select
Range("C3").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub
--
n00b lookn for a handout :)
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 119
Default Refering to new sheet name
first, do you really need to put the values in L3, N3 and P3? or could this
be rewritten as:
Sub sheetname()
'
' sheetname Macro
' Macro recorded 9/10/2007 by a2holder
'
dim newName as string

ActiveSheet.Unprotect
newName = cells(2,7) & cells(3,13) & cells(3,7) & cells(3,15) & cells(4,7)
' Clean up name for illegal characters and make sure it isn't too long
(31 characters)
newName = Replace(newName, ":", "-")
newName = Replace(newName, "/", "-")
newName = Replace(newName, "\", "-")
newName = Replace(newName, "*", "-")
newName = Replace(newName, "?", "-")
newName = Replace(newName, "[", "-")
newName = Replace(newName, "]", "-")
newName = Left(newName,31)
ActiveSheet.Name = newName

ActiveSheet.Protect
End Sub

You can call sheetname before you unprotect the active sheet and then it
will still be the active sheet on return so you can get the name by reading
ActiveSheet.Name like:

dim CurrSheet as string
call sheetname
ActiveSheet.Unprotect
CurrSheet = ActiveSheet.name
' Now reference sheets(CurrSheet)

Hope this helps

Peter Richardson


"Arnie" wrote:

I have a macro that renames a tab based on some cell contents from the same
sheet (see code below). I need to insert this routine into the beginning of
another macro that filter/copy/pastes information on the sheet based on the
same cell contents but I can't figure out how to refer to the sheet after it
has just been renamed by the first routine. I'm very clumsy with variables
but I'm guessing this is what I need. Any ideas? I'll include the second
portion of the routine as well.

This is the renaming macro:

Sub sheetname()
'
' sheetname Macro
' Macro recorded 9/10/2007 by a2holder
'

'
ActiveSheet.Unprotect
Range("G2").Select
Selection.Copy
Range("L3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("G3").Select
Application.CutCopyMode = False
Selection.Copy
Range("N3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("G4").Select
Application.CutCopyMode = False
Selection.Copy
Range("P3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("Q3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 =
"=CONCATENATE(RC[-5],RC[-4],RC[-3],RC[-2],RC[-1])"
With ActiveSheet
.Name = .Range("Q3").Value
End With
ActiveSheet.Protect
End Sub


This is the "filtering" macro:

Sub Display1()
'
' Display1 Macro
' Display analytical sheet 091406
'

'
ActiveSheet.Unprotect
Rows("7:7").Select
Selection.EntireRow.Hidden = False
Rows("6:6").Select
Selection.EntireRow.Hidden = False
Sheets("Database").Visible = True
Sheets("WORKSHEET 1").Visible = True
Sheets("WORKSHEET 2").Visible = True
Sheets("WORKSHEET 1").Select
Rows("3:6").Select
Selection.ClearContents
Range("A3").Select
Sheets("Database").Columns("A:AU").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A3"), Unique:=False
Sheets("WORKSHEET 2").Select
Range("A5:S52").Select
Selection.Copy
Sheets("Line 1").Select
Range("A8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A14:P55").Select
Application.CutCopyMode = False
Range("A14:P55").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("B6:B7"), Unique:=False
Rows("6:7").Select
Selection.EntireRow.Hidden = True
Sheets("Database").Select
Sheets("Database").Visible = False
Sheets("WORKSHEET 1").Visible = False
Sheets("WORKSHEET 2").Visible = False
Sheets("Line 1").Select
Range("C3").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub
--
n00b lookn for a handout :)
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 65
Default Refering to new sheet name
Thanks Joel! That's exactly what I needed!
--
n00b lookn for a handout :)


"Joel" wrote:

Arnie: You renamed the code in the code below

With ActiveSheet
.Name = .Range("Q3").Value
End With

Just add a new variable anme

With ActiveSheet
.Name = .Range("Q3").Value
NewSheetName = .name
End With

Later refer to sheet by new name

sheets(NewSheetName).activate

or
worksheets(NewSheetName).activate

No quotes are required around NewSheetName because it is a variable.
"Arnie" wrote:

I have a macro that renames a tab based on some cell contents from the same
sheet (see code below). I need to insert this routine into the beginning of
another macro that filter/copy/pastes information on the sheet based on the
same cell contents but I can't figure out how to refer to the sheet after it
has just been renamed by the first routine. I'm very clumsy with variables
but I'm guessing this is what I need. Any ideas? I'll include the second
portion of the routine as well.

This is the renaming macro:

Sub sheetname()
'
' sheetname Macro
' Macro recorded 9/10/2007 by a2holder
'

'
ActiveSheet.Unprotect
Range("G2").Select
Selection.Copy
Range("L3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("G3").Select
Application.CutCopyMode = False
Selection.Copy
Range("N3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("G4").Select
Application.CutCopyMode = False
Selection.Copy
Range("P3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("Q3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 =
"=CONCATENATE(RC[-5],RC[-4],RC[-3],RC[-2],RC[-1])"
With ActiveSheet
.Name = .Range("Q3").Value
End With
ActiveSheet.Protect
End Sub


This is the "filtering" macro:

Sub Display1()
'
' Display1 Macro
' Display analytical sheet 091406
'

'
ActiveSheet.Unprotect
Rows("7:7").Select
Selection.EntireRow.Hidden = False
Rows("6:6").Select
Selection.EntireRow.Hidden = False
Sheets("Database").Visible = True
Sheets("WORKSHEET 1").Visible = True
Sheets("WORKSHEET 2").Visible = True
Sheets("WORKSHEET 1").Select
Rows("3:6").Select
Selection.ClearContents
Range("A3").Select
Sheets("Database").Columns("A:AU").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A3"), Unique:=False
Sheets("WORKSHEET 2").Select
Range("A5:S52").Select
Selection.Copy
Sheets("Line 1").Select
Range("A8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A14:P55").Select
Application.CutCopyMode = False
Range("A14:P55").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("B6:B7"), Unique:=False
Rows("6:7").Select
Selection.EntireRow.Hidden = True
Sheets("Database").Select
Sheets("Database").Visible = False
Sheets("WORKSHEET 1").Visible = False
Sheets("WORKSHEET 2").Visible = False
Sheets("Line 1").Select
Range("C3").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub
--
n00b lookn for a handout :)
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
Refering to a sheet in a cell Jarek Kujawa[_2_] Excel Discussion (Misc queries) 0 July 31st 08 11:06 AM
REFERING TO CELL IN DIFFERENT SHEET cjbarron5 Excel Discussion (Misc queries) 2 May 28th 08 04:32 AM
Refering to a Sheet Name in a macro tedd13 Excel Programming 3 July 19th 06 04:36 PM
refering to previous sheet [email protected] Excel Discussion (Misc queries) 3 January 25th 06 02:55 PM
Problem refering to Add-In sheet N E Body[_21_] Excel Programming 4 August 20th 05 06:34 PM


All times are GMT +1. The time now is 07:54 PM.

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"

 

Contact Us ExcelBanter forum home Privacy Statement