Refering to new sheet name

G

Guest

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
 
G

Guest

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 said:
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
Click to expand...
 
G

Guest

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 said:
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
Click to expand...
 
G

Guest

Thanks Joel! That's exactly what I needed!
--
n00b lookn for a handout :)


Joel said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error 1004 paste method of worksheet class failed 3
conso macro 0
Run macro from active workbook whose name keeps changing 20
Copy Paste Macro not Pasting Values when Blank 3
Advanced Filter works in recorder but not while running macro 5
File Name changes with dates and Deleting Rows of Zeros 0
Simplify Code for Copy/Paste Special 11
Copy paste - Values - formula - Optimise 2

Top