ExcelBanter - Refering to new sheet name

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Refering to new sheet name (https://www.excelbanter.com/excel-programming/397261-refering-new-sheet-name.html)

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 :)

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 :)

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 :)

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 :)