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